extracting numbers within text string!

  • Thread starter Thread starter via135
  • Start date Start date
V

via135

hi!

given below is my sample data thru A1:A4

SB (CLO 100
LIEN 2000
SB (CLOSE) MAT 30000
*CLOSE 100 SB

what I want is extraction of numbers alone thru B1:B4
like

100
2000
30000
100

is this possible by using worksheet function?

hlp pl!?

-via135
 
If there will only be ONE string of numbers in the cell text, then try this:

For text in A1
B1:
=--(0&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1)),1)))))

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
Here's another way...

=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))

Note that both my formula and Ron's will omit leading zeros, if they
exist. If you'd like to keep them when they exist, try...

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUMPRODUCT((LE
N(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))))

Hope this helps!
 
yes Domenic!

its works like a charm!

BTW can u pl explain the rational behind using the expression
"9.99999999999999E+307" just for acadamic interest?!

-via135
 
It's the largest number that Excel recognizes, and is unlikely to occur
within the lookup range. With this number as its lookup value, LOOKUP
returns the last numerical value in the lookup range.

Hope this helps!
 
Back
Top