Formula to pick out number values

[JaneC]

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

[wjohnson]

Try This:
=IF(ISNUMBER(SEARCH("70001",A1)),"OK", "Not OK")
The formula doesnt seem to care if it is a number or text.
Change "70001" to whatever you want, Same with "OK" and "NOT OK"
A1 should change automatically as you copy down.
You can also find more in EXCEL Help - if you type "Contains" in the FIND Area of HELP

[Max]

"JaneC" wrote:
> ... For example I want to get the numbers
> in the following strings into a cell on their own:
> Colrain 17001
> 17001 Colrain

Focusing on the above lines,
perhaps you might also want to try this alternative
taken from a past post by Peo in 2003
(re: http://tinyurl.com/a6v8s )

Assuming data in A1 down,

Put in B1, array enter the formula
(i.e. press CTRL+SHIFT+ENTER):

=MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM(1
*ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1))))*1

Copy B1 down


Peo's formula seems to work fine on the data you posted ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[Domenic]

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 <[email protected]>,
"JaneC" <[email protected]> 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

[Ron Rosenfeld]

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