function to extract numbers

E

ela

13133..13558 (-)(426bp)
13714..13995 (-)(282bp)


match, exact, left, right, mid cannot help me extract the pairs of 13133,
13558; and 13714, 13995

is there any function rather than writing regular expression in VBscript (if
any) to achieve the purpose?
 
J

Jarek Kujawa

=LEFT(A1,5)
to extract 13133

and

=MID(A1,FIND(" (-)",A1,1)-5,5)
to extract 13558

HIH
 
R

Roger Govier

hi ela

Have you tried
=Left(A1,find(".",A1)-1) to get 13133
and
=MID(A1,FIND(".",A1)+2),5) to get 13358

or, if you wanted them as a pair, comma separated, then
=SUBSTITUTE(LEFT(A1,FIND(" ",A1)-1),"..",",")
 
R

Ron Rosenfeld

13133..13558 (-)(426bp)
13714..13995 (-)(282bp)


match, exact, left, right, mid cannot help me extract the pairs of 13133,
13558; and 13714, 13995

is there any function rather than writing regular expression in VBscript (if
any) to achieve the purpose?
Click to expand...

Data/Text-to-Columns can split that up
Delimited
Select <space>
<other> . (enter a dot)
Treat consecutive delimiters as one

--ron
 
Top