Using (right) function to find letter not number

Hi,

We have a number of cells in a column similar to these:

ABC123
ABC12E
ABC2A3

I am trying to find out the letter that is furthest to the right in these strings.

Example:

1. would be C
2. would be E
3. would be A

I know the RIGHT function will pull any character is on the right, but is there one for just letters and not numbers?

Thanks

Hi,

May be something like this with the help of some helper columns?

Put this new function into a MODULE (same place regular macros go)

Please Login or Register  to view this content.

Now, if the code ABC12E is in cell A1, the formula to find E would be:

=RIGHT(StripNumber(A1),1)

try this array formula, use CTRL+SHIFT+ENTER to commit.

=MID(B3,MAX(IF(ISERROR(CODE(MID(B3,COLUMN(1:10),1))),0, IF(CODE(MID(B3,COLUMN(1:10),1) )<65,0,COLUMN(1:10)))),1)

Will return #Value if no letters in text being searched.

Here's an alternative:

=MID(A1,LOOKUP(2,1/(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>57),ROW(INDIRECT("1:"&LEN(A1)))),1)

Richard