Extracting numbers from a combined text & number string

**astrikor** · 05-25-2007, 07:58 AM

I am stumped trying to identify a combination of functions for removing text characters from a combined text & number string.

For example, I have string 199H and I want to remove the H and be left with the number 199 in number format for subsequent calculations.

The numerical part of the strings are of variable length, and may or may not have a single text character to be removed from the right hand side.

Any ideas??

**sweep** · 05-25-2007, 08:11 AM

Hi,

If your string is in A1,

=IF(AND(CODE(RIGHT(A1))>=64,CODE(RIGHT(A1))<=122),VALUE(LEFT(A1,LEN(A1)-1)),A1)

will test to see if the right hand character is a letter, and if so, return only the numeric part as a value.