extracting numbers within text string!

[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

 

[Guest]

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

 

[Domenic]

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!

 

[via135]

thks Ron!
stupendous indeed!

-via13

 

[via135]

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

 

[Domenic]

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!

 

[via135]

thks again Domenic !
for the informative reply !!

-via13