If last character is Upper Case

[jespo1351]

I am looking for a formula to detect if the last character of a cell is upper case and remove it if it is upper case. If it is not upper case, it will remain the same.

IF A1 Contains CheeseDoodlesR - I would like A2 to populate CheeseDoodles

Any help is greatly appreciated. I tried playing around with exact/right/upper but I can't connect any formulas together.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

Fluff.xlsm
	A	B
1
2	CheeseDoodlesR	CheeseDoodles
3	CheeseDoodlesr	CheeseDoodlesr
Sheet5

Cell Formulas
Range	Formula
B2:B3	B2	=IF(EXACT(UPPER(RIGHT(A2)),RIGHT(A2)),LEFT(A2,LEN(A2)-1),A2)

 

[Jeffrey Mahoney]

Please try this

=IF(UPPER(RIGHT(D2,1))=RIGHT(D2,1),LEFT(D2,LEN(D2)-1),D2)

 

[Fluff]

The IF function is not case sensitive so that won't work. You need to use Exact

 

[Jeffrey Mahoney]

I like this better because it doesn't remove other characters (non-alphabet).

=IF(AND(CODE(D2)>64,CODE(D2)<91),LEFT(D2,LEN(D2)-1),D2)

Capital Letters A-Z are ascii code 65-90

 

[Fluff]

That only looks at the 1st character in the cell, not the last.

 

[Jeffrey Mahoney]

I got in a hurry, thanks Fluff

=IF(AND(CODE(RIGHT(D2,1))>64,CODE(RIGHT(D2,1))<91),LEFT(D2,LEN(D2)-1),D2)

 

[Jeffrey Mahoney]

If we knew the OP had office 360, I would use the LET function to shorten that

 

[Scott Huish]

=LEFT(A1,LEN(A1)-EXACT(RIGHT(A1),UPPER(RIGHT(A1))))

 

[Scott Huish]

The CODE version could be shortened to this:
=LEFT(A1,LEN(A1)-AND(CODE(RIGHT(A1))>64,CODE(RIGHT(A1)<91)))