Remove space and trailing text from number

Giganews Newsgroups
Subject: Remove space and trailing text from number
Posted by:  gossamer (gossamerpro@nospam.cox.net)
Date: Wed, 1 Oct 2003

Hi ng
Using xl xp pro.
Receive text file wish to convert to xl

Some data comes in like this:
12 oz

Would like to trim " oz" w/o quotes
So all I'm left with is 12

Here's my formula:
=IF(ISNUMBER(A3),A3,(VALUE(LEFT(A3,FIND(" ",A3)-1))))
Works fine.

However other data comes in this format:
12+z
How could I re-work my formula to take care of either condition

Remaining value beginning from left of string could be any number of digits

Also:
If col a contains text I receive #value! error
I take care of this with
=IF(ISNUMBER($F1037),$F1037,1)
But it would be more elegant if I could incorp all into 1 formula.

tia.
goss

Replies