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))

 

[rlv01]

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))

If the number pattern of only having one letter at the end of a digit string holds, you can take advantage of that to simplify the formula.

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

 

[Rick Rothstein]

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

=LET(t,TRIM(E238),LEFT(t,LEN(t)-ISERROR(-RIGHT(t,1)))+0)

Do you really need the LET and TRIM function calls? This seems to work correctly as far as I can tell...

=0+LEFT(E238,LEN(E238)-ISERR(-RIGHT(E238)))

 

[Peter_SSs]

Do you really need the LET and TRIM function calls?

Depends if there might be any spaces at the right of the original text. Since the OP was using TRIM, I thought that may be a possibility so continued that way.

 

[Tom.Jones]

Try:

=IF(N(A1),A1,--MID(A1;1,LEN(A1)-1))

 

[Phuoc]

Another way:

=AGGREGATE(14,6,--LEFT(A1,LEN(A1)-{1,0}),1)

 

[psycoperl]

Depends if there might be any spaces at the right of the original text. Since the OP was using TRIM, I thought that may be a possibility so continued that way.

The system that I am pulling data from can have padded spaces at the front or the end of the string and for sorting purposes I don’t want any of those padding spaces.

 

[Phuoc]

Or

=-LOOKUP(1,-LEFT(A1,LEN(A1)-{1,0}))

 

[Peter_SSs]

Looks like many suggestions have not allowed for this possibility ..

The system that I am pulling data from can have padded spaces at the front or the end of the string