extracting numbers within text string!

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


"via135" wrote:

>
> 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
>
>
> --
> via135
> ------------------------------------------------------------------------
> via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
> View this thread: http://www.excelforum.com/showthread...hreadid=539020
>
>

thks Ron!
stupendous indeed!

-via135

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!

In article <via135.27aqxn_1146768903.1221@excelforum-nospam.com>,
via135 <via135.27aqxn_1146768903.1221@excelforum-nospam.com> wrote:

> 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

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

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!

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!

In article <via135.27athc_1146772207.3577@excelforum-nospam.com>,
via135 <via135.27athc_1146772207.3577@excelforum-nospam.com> wrote:

> 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

thks again Domenic !
for the informative reply !!

-via135