- You are here
- Microsoft Newsgroups Archived.At
- microsoft.public.excel.misc
- 2003 October
- Remove space and trailing text from number

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

- Re: Remove space and trailing text from number posted by Bernard V Liengme on Wed, 1 Oct 2003