Identify the last character as UPPER or LOWER case

[bobgrand]

Hello all,
I am trying to identify if the last character of the bin location is upper or lower case. I would like to see the answer in column C.

Can anyone shed some light on how to do this? Any help would be greatly appreciated

	A	B	C
1	ITEM	BIN LOCATION	UPPER/LOWER
2	WIDGET1	01A02B	UPPER
3	WIDGET2	01A06d	LOWER
4	WIDGET3	05B06e	LOWER
5	WIDGET4	10D02B	UPPER

<tbody>
</tbody>

 

[Marcelo Branco]

Try

C2 copied down
=IF(EXACT(RIGHT(B2),UPPER(RIGHT(B2))),"UPPER","LOWER")

M.

 

[bobgrand]

Thank you Marcelo, Huge help

Have a great day

 

[Rick Rothstein]

Assuming your cells could have blanks or numbers not ending in a letter...

=IF(AND(CODE(RIGHT(B2))>64,CODE(RIGHT(B2))<91),"UPPER",IF(AND(CODE(RIGHT(B2))>96,CODE(RIGHT(B2))<123),"LOWER",""))

If the only possibilities are letters, then this...

=IF(AND(CODE(RIGHT(B2))>64,CODE(RIGHT(B2))<91),"UPPER","LOWER""")

 

[Marcelo Branco]

Thank you Marcelo, Huge help

Have a great day

You are welcome, Thanks for the feedback.

M.

 

[bobgrand]

Hi Rick,
You got my wheels spinning,,, Let me give you a little background on how we structure our bin locations. They can be no less than 3 to no more than 6 characters in length. Most of our locations use a 6 character format. (##L##L - 05D08G) The last character of the 6 character format is always a letter either UPPER or LOWER case. Lower case means the material is stocked in multiple areas. We start with a zero to keep the sorting true. We use a tab separated format so there are no blanks at the end of the bins that are 3, 4 or 5 characters in length.

Is it possible to have the results show "UPPER", "LOWER", or "NUMBER"

Thank you for your help and looking forward to your response.

Bob

 

[Rick Rothstein]

Is it possible to have the results show "UPPER", "LOWER", or "NUMBER"

Give this a try...

=IF(AND(CODE(RIGHT(B2))>64,CODE(RIGHT(B2))<91),"UPPER","LOWER""")

 

[JackDanIce]

Try:

=IF(CODE(RIGHT(B2))=MEDIAN(CODE(RIGHT(B2)),48,57),"NUMBER",IF(CODE(RIGHT(B2))=MEDIAN(CODE(RIGHT(B2)),65,90),"UPPER","LOWER"))

 

[Rick Rothstein]

Give this a try...

=IF(AND(CODE(RIGHT(B2))>64,CODE(RIGHT(B2))<91),"UPPER","LOWER""")

Well, that did not come out right. I thought I was copy/pasting the following, but I got the above instead. Here is what I meant to post...

[table="width: 500"]
[tr]
	[td]=IF(AND(CODE(RIGHT(B2))>64,CODE(RIGHT(B2))<91),"UPPER",IF(ISNUMBER(0+RIGHT(B2)),"NUMBER","LOWER"))[/td]
[/tr]
[/table]

 

[bobgrand]

Thank you Rick,

That's perfect


Bob