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

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
------------------------------------------------------------------------
wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640
View this thread: http://www.excelforum.com/showthread...hreadid=499278

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


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640
View this thread: http://www.excelforum.com/showthread...hreadid=499278

"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
( 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
--

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

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