Formula to pick out number values

[Guest]

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"
Just change "70001" to what to search for.
Change "OK" and "Not Ok" to what you want your error messages to be.
The formula doesn't seem to care if it is a "Number" or "Text"
If you type "Contains" in the EXCEL - HELP File - you should find some
examples.

 

[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]

... 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 ..

 

[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!

 

[Ron Rosenfeld]

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