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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ITEM[/TD]
[TD]BIN LOCATION[/TD]
[TD]UPPER/LOWER[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]WIDGET1[/TD]
[TD]01A02B[/TD]
[TD]UPPER[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]WIDGET2[/TD]
[TD]01A06d[/TD]
[TD]LOWER[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]WIDGET3[/TD]
[TD]05B06e[/TD]
[TD]LOWER[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]WIDGET4[/TD]
[TD]10D02B[/TD]
[TD]UPPER[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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