Try...

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

Note that the formula will not return leading zeros. For example, if we
have...

Colrain 017001

....the formula will return 17001.

Hope this helps!

In article ,
"JaneC" wrote:

Hi,

Is there a formula I could use that pick out only numbers in a mixed format
cell, no matter where in the cell the number is? I know of the mid formula,
but i will have to change the starting point on every row. For example I want
to get the numbers in the following strings into a cell on their own:

Colrain 17001
17001 Colrain

Thanks,

Jane