On Sun, 8 Jan 2006 22:06:03 -0800, "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

Download and install Longre's morefunc.xll free add-in from
http://xcell05.free.fr/

Then use this formula:

=REGEX.MID(A1,"\d+")

The formula returns the number as a "string". If you need it to be a number
that can be used in excel functions, then use:

=--REGEX.MID(A1,"\d+")

or

=VALUE(REGEX.MID(A1,"\d+"))

to convert it to a number.


--ron