Find first Alpha Character from right side of a string

[Kmac224]

Hi All,

I'm trying to use a formula to find the first alpha character from the right of a string.

For example, from the below, I'd expect "C" returned.

[TABLE="width: 121"]
<colgroup><col width="161" style="width: 121pt; mso-width-source: userset; mso-width-alt: 5888;"> <tbody>[TR]
[TD="width: 161, bgcolor: transparent"]BABA US 09/21/18 C165[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!!

 

[Ingolf]

Good catch.

I don't believe that you can leave off the CSE for the D1 formula. For example, try putting a C somewhere near the front of the A1 value. It gets found, but not the later C.

You're right.

 

[Rick Rothstein]

Doesn't seem to work for an all alpha entry like "abc"

I had not assumed that was a possibility. The fix is easy enough though (still normally-entered)...

=IFERROR(RIGHT(TRIM(SUBSTITUTE(A1,LOOKUP(9E+99,--RIGHT(A1,ROW($1:$99))),""))),RIGHT(TRIM(A1)))

 

[Rick Rothstein]

I had not assumed that was a possibility. The fix is easy enough though (still normally-entered)...

=IFERROR(RIGHT(TRIM(SUBSTITUTE(A1,LOOKUP(9E+99,--RIGHT(A1,ROW($1:$99))),""))),RIGHT(TRIM(A1)))

By the way, I included those TRIM function calls because I thought I "saw" a space character on the end of the text I copied, so I thought I would protect against it, but now I do not see that character so I am not sure what I did originally to get it. Anyway, if the data is pure and there will never be any trailing spaces, you can use this slightly shorter formula....

=IFERROR(RIGHT(SUBSTITUTE(A1,LOOKUP(9E+99,--RIGHT(A1,ROW($1:$99))),"")),RIGHT(A1))


EDIT NOTE: I should point out that the above formula and the ones in the message above it and in Message #8 all assume the number at the end of the text (when there are numbers on the end) will never have a leading zero at the front of them.