Remove space and trailing text from number

[gossamer]

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

 

[Bernard V Liengme]

Hello,
This VBA function will do it

Function tonumber(mytext)
temp = Val(mytext)
tonumber = temp
End Function

If you need help in using it please get back to me
Bernard