Solved: formula to extract number

[tkaplan]

hi all,

just need quick help with a formula - i have a cell (a1) with a bunch of info. i want b1 to just extract the digits from a1, not the text or symbols. the digits can be anywhere in the cell.
i.e. a1 can be test 1 cell, i want b1 to return 1 or if a1 is test 41 i want b1 to return 41 etc.
thanks!

[mdmackillop]

Try a User Defined Function.
Copy this code to a standard module. Enter =GETNOS(A1)

Function GetNos(Data As Range)
Dim Num, i As Long
For i = 1 To Len(Data)
If Mid(Data, i, 1) Like ("#") Then Num = Num & Mid(Data, i, 1)
Next
GetNos = --Num
End Function

[Bob Phillips]

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))))

[Aussiebear]

OKay, I'll be the fall guy......

How does that one work Bob? :dunno

[Bob Phillips]

It is pretty straightforward if you break it down.

It's basic premise is that it looks for all number positions in the string and takes the minimum

MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")

it appends "123456789" to ensure that every digit is found somewhere.

Form this array of number positions, it extracts every numeric string possibility using the len of the string as its controller (the string could be all numeric), with this code

MID(A1, ... ,ROW(INDIRECT("1:"&LEN(A1)))

which are strings, so it coerces it to numbers

--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

The -- does the coercing, the "0"& is in case there are no numbers in the string.

Finally, it gets the biggest number by looking up a very large number, LOOKUP will find the largest number smaller than the lookup number.

Example

Text 41

SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") gets an array of {8,7,10,11,6,13,14,15,16,17}

MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")) simply gets the 6

MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))) then returns the array {"4";"41";"41";"41";"41";"41";"41"}

which coerced to numbers in --("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))) gives us {4;41;41;41;41;41;41}

LOOKUP 99^^99 in this array, and we get 41.

Voila!

[tkaplan]

thank you all!

[Aussiebear]

Hi tkaplan, If you believe that this issue is solved to your satisfaction, please use the thead tools to mark this as "Solved".

Thank you.