Separate numbers from text in column

[JoeSpareBedroom]

I've got a column of data in which most cells contain numbers and text, like
the samples shown below. I need to use the numerical portion for some math.
A few cells contain just a number, but most are a mix. Pretty sloppy data
input, but there's nothing I can do about that. I have to work with what
I've been given. Anyone know a way to extract just the numbers to a separate
column? I don't see anything obvious in the list of Excel functions. I'm
using Excel 2000.

Sample data:
24 RL
80 USE
9.25Z
19.76
14 OZ
17.4OZ
15.6Z

 

[Celticshadow]

Hi Joe

This was given to me by Rick Rothstein on another thread I posted. Copy and
paste the formula then autofill.

B1 - =LOOKUP(9000000000+307,--LEFT(A2,ROW($1:$99)))

Kind Regards

Celticshadow

 

[JoeSpareBedroom]

Thank you! This worked like a charm. No idea how, though. I'll have to study
it further. This is a great trick to keep for future use.

 

[ShaneDevenshire]

Hi,

If you're going to study it, you can simplify it for your sample data to read:

=LOOKUP(1E+30,--LEFT(I5,ROW($1:$9)))

Nobody take offense, the formula is great and works like a charm - this is
just to make it easier if Joe tries to understand what this formula is doing:

The entry that was given you should have been of the form
=LOOKUP(9E+307,--LEFT(A2,ROW($1:$99)))
somewhere along the way the E was lost. In this case it didn't make any
difference the result of adding =9000000000+307 is a pretty large number so
there is no problem. But the fact that the original formula contained +307
tells me that the original formula was ment to be in scientific notatation,
the number 9E+307 is much much larger than =9000000000+307 . If the original
formula creator had wanted to use =9000000000+307 they could have written it
=9000000307 , it would have done exactly the same thing, or they could just
have written =9000000000.

There is also some discussion of LOOKUP in the post 4 or 5 above this one On
finding the last item in a row or column.