Corey,

My formula actually returns 11 from the string OFF 11-4005 as it strips off
anything before the first numeric, and anything after the first non-numeric
after that.

Did you insert the name formula, and array enter everything?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Corey" wrote in message
...
This works perfect Peo! Thanks a bunch! Also to you Domenic for coming up
with that original formula. I tried Bob's suggestion to this as well, but
got
#VALUE!. Since your formula returns the everything to the right, starting
with the first numeric, I just added VALUE(LEFT(A1,2)) to pull out the
first
two numbers. This is the division number I've been trying to get at.
Perhaps
if I said that earlier, there's another way to get it done. ?? Anyways,
I've
also added two if statements to the beginning of that and it works great.
When I try to add a third I get an error that leads me to the "SEARCH"
portion of the formula. Is there a limit to how many conditionals I can
put
in this thing?

Thanks again!

"Peo Sjoblom" wrote:

The reason the first returns 769142 is that the extracted value 11-4005
is seen as a date so Excel translates the date as 11/01/05 which is
769142
days after Jan 0 1900 Excel's first date, given that the
numbers can be so different it is hard to guard against, try this
adaptation
of Domenic's excellent formula


=LOOKUP(REPT("z",255),MID(A1,MIN(SEARCH({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789
")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))


--

Regards,

Peo Sjoblom

"Corey" wrote in message
...
Thanks for all the suggestions people. I'm not to familiar with VBA
nor
UDF.
Also, I'm trying to put together a tool for a below novice user of
Excel
as
it takes her hours to decipher a statement. I was hoping to use just a
simple
formula and have her cut and paste the data to have it automatically
populate
with this formula. I've tried yours Domenic, and it seems to work for
the
most part, except for some:

OFF 11-4005 returns 769142
ON 040652 returns 40652 (losing the first 0)




"Domenic" wrote:

If your numbers are not more than 15 digits in length, try...



=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15}))

Otherwise, 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)))))

Hope this helps!

In article ,
"Corey" wrote:

Hello. I have a column with data that contain numerics and text:

ON 3127
ON2679
O/F 20R
OFF///130H

Is there a formula I can input in an adjacent cell to only pull
out
the
numbers to give me the following:

3127
2679
20
130

Any help is appreciated. Thanks!