When I use your formula I get 769142 not 114005 also if you use number you
won't get a leading zero as in the OP's second example
--
Regards,

Peo Sjoblom

(No private emails please)


"Bob Phillips" wrote in message
...
No Peo, it still gives 114005, it just makes the result a number as
against
the string. The reason I even mentioned it was because in Domenic's
original; formula, the -- was by the MID, and as you showed, that won't
work, by preceding it does.

--

HTH

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


"Peo Sjoblom" wrote in message
...
I believe the OP complained that he got 769142 instead of 11-4005 thus
the
change, otherwise one might as well keep Domenic's original formula


--

Regards,

Peo Sjoblom

"Bob Phillips" wrote in message
...
You might want to convert that to a number



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


--

HTH

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


"Peo Sjoblom" wrote in message
...
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!