That sounds like too many nested functions. Put some of the tests in a
separate cell, and then include a test on that cell in the main formula.
That is exactly the problem I got with my formula, which is what I use the
name formula for.

--

HTH

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


"Corey" wrote in message
...
A little more info on my problem:

This formula works:

=IF(VALUE(RIGHT(C3381,3))=107,7,IF(LEFT(N3381,8)=" OFF ROAD","MANUAL

INPUT",VALUE(LEFT(LOOKUP(REPT("z",255),MID(N3381,M IN(SEARCH({0,1,2,3,4,5,6,7
,8,9},N3381&"0123456789")),{1,2,3,4,5,6,7,8,9,10,1 1,12,13,14,15})),2))))

By adding to more conditions to the front of it, I get an error:


=IF(N3381="M8",35,IF(VALUE(RIGHT(C3881,3))=814,33, IF(VALUE(RIGHT(C3381,3))=1
07,7,IF(LEFT(N3381,8)="OFF
ROAD","MANUAL

INPUT",VALUE(LEFT(LOOKUP(REPT("z",255),MID(N3381,M IN(SEARCH({0,1,2,3,4,5,6,7
,8,9},N3381&"0123456789")),{1,2,3,4,5,6,7,8,9,10,1 1,12,13,14,15})),2))))))




"Corey" wrote:

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!