Remove Last Character only if not a number

[psycoperl]

I am trying to clean up for sorting purposes class catalog #s. To that end, I want to remove any non numeric characters that are at the end of these numbers. For example 2006L should be just 2006, and 2610 would be 2610. Values can range from 1 to 5 characters in length. I tried to use the following formula

=IF(ISNUMBER(RIGHT(TRIM(E240),1)),TRIM(E240),LEFT(TRIM(E240),LEN(TRIM(E240))-1))

however it would remove the last character if it was also a digit.

For example 9900 became 990, 9901 became 990, 9902 became 990 also.

Where did I go wrong in the formula?

Book1
	C	D	E				I
238	BAR01	MTH	9902				990
239	BAR01	MTH	9903				990
240	BAR01	MTH	122L				122
241	BAR01	MTH	123L				123
242	BAR01	MTH	132L				132
243	BAR01	MTH	150B				150
244	BAR01	MTH	2002T				2002
245	BAR01	MTH	2006L				2006
Sheet1

Cell Formulas
Range	Formula
I238:I245	I238	=IF(ISNUMBER(RIGHT(TRIM(E238),1)),TRIM(E238),LEFT(TRIM(E238),LEN(TRIM(E238))-1))

 

[Fluff]

How about

=IF(ISNUMBER(--RIGHT(TRIM(E238),1)),TRIM(E238),LEFT(TRIM(E238),LEN(TRIM(E238))-1))

Right will always return text so the ISNUMBER will return False.

 

[arthurbr]

Perhaps =IF(ISTEXT(right(E2,1), Left(E2,LEN(E2)-1)+0,E2)

 

[psycoperl]

Thank you. I also looked at another forum (not sure if I can name it here) and got the idea to use this formula which worked.

=IF(ISNUMBER(RIGHT(TRIM(E255),1)*1),TRIM(E255),LEFT(TRIM(E255),LEN(TRIM(E255))-1))

 

[Fluff]

That is basically doing what I suggested, but in a less efficient way.

 

[psycoperl]

That is basically doing what I suggested, but in a less efficient way.

@Fluff I did not understand your syntax with the "--" and what it was doing.

 

[Fluff]

In this instance it converts a text number into a number & returns an error if it is a character.

 

[Peter_SSs]

Some other simpler options, depending on whether you can use your 365 version or not.

23 02 23.xlsm
	E				I	J
238	9902				9902	9902
239	9903				9903	9903
240	122L				122	122
241	123L				123	123
242	132L				132	132
243	150B				150	150
244	2002T				2002	2002
245	2006L				2006	2006
Num

Cell Formulas
Range	Formula
I238:I245	I238	=LET(t,TRIM(E238),LEFT(t,LEN(t)-ISERROR(-RIGHT(t,1)))+0)
J238:J245	J238	=LEFT(TRIM(E238),LEN(TRIM(E238))-ISERROR(-RIGHT(TRIM(E238),1)))+0

 

[psycoperl]

We have office 365. Could you please explain these formulas? I would like to know how they are working

 

[Peter_SSs]

I would like to know how they are working

Have you tried using the 'Evaluate Formula' tool on the formulas ribbon tab?