Finding the NUMBER within a string

[dwarnimont]

What creative formula will be able to find and extract only the number from a cell with string like this: abc1233efg. Answer is 1233. length and number of characters is not fixed.

 

[Scott Huish]

=lookup(9.999999999e+307,mid(a1,min(find({0,1,2,3,4,5,6,7,8,9},a1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})+0)

Excel 2010
	A	B
1	abc1233efg	1233
2	a14z	14
3	This one is 23	23
Sheet1

Cell Formulas
Range	Formula
B1	=LOOKUP(9.999999999E+307,MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})+0)
B2	=LOOKUP(9.999999999E+307,MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})+0)
B3	=LOOKUP(9.999999999E+307,MID(A3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})+0)

 

[dwarnimont]

This is insane!!!! I cant believe you created it. I'd love to understand the pieces better. Where might i go to learn the components of this formula?

 

[Scott Huish]

On your spreadsheet in the cell where the formula is. On the Formula tab, click on Evaluate Formula, then keep clicking Evaluate until it gets to the answer and you can see what it is doing.

But basically, it's finding the position of the first number, then making an array of 1 to 15 character strings. It adds 0 to try and coerce each of these values to a number. LOOKUP(9.99999999E+307 will find the last number in this array while ignoring errors and this is your number.