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

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&"012 3456789")),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

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&"01234 56789")),SUMPRODUCT((LE
N(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))))

Hope this helps!

In article ,
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

thks Ron!
stupendous indeed!

-via135


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=539020

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&"01234 56789")),SUMPRODUCT((LE
N(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))))

Hope this helps!


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=539020

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 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


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=539020