Formula to Find the Position of the Last Alpha Character in a Text String?

[mcgrupp]

I am looking to see if there is a simple formula that will return the position of the last alpha character in a text string. The text strings will be of varying lengths and will contain alphanumeric characters and spaces only. So one string might be: 123 45 Abcde 678

So I just want to return the postion of letter “e” in this case, but it could be any of the alpha characters ABCDEFGHIJKLMNOPQRSTUVWYZabcdefghijklmnopqrstuvwxyz for the cells in my column.

I am just trying to avoid a lengthy formula.
Thanks for any help provided.

 

[VBA Geek]

one possibility, with your string in E1


=LOOKUP(1E+100,LOOKUP(CODE(MID(UPPER(E1),ROW(INDIRECT("1:"&LEN(E1))),1)),ROW(65:90)),ROW(INDIRECT("1:"&LEN(E1))))

 

[mcgrupp]

All hail! Worked perfectly.

 

[mcgrupp]

One more thing if I can, I could feasibly use this any time to determine if a string contains an alpha character since I would return #N/A if there wasn't an alpha character, right?

 

[Domenic]

I think the formula offered by VBA Geek will fail in certain circumstances. For example, the formula will return 14 instead of 12 for the text string...

123 45 Abcde \ 678

Maybe...

=MATCH(2,1/(ABS((CODE(UPPER(MID(E1,ROW(INDIRECT("1:"&LEN(E1))),1)))-77.5))<=12.5))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[István Hirsch]

Enter this formula with Ctrl + Shift +Enter:

=LEN(A1)-MATCH(TRUE,MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1)>="A",0)+1

 

[mcgrupp]

Domenic, your formula worked as well. What exactly would cause VBA's formula to fail? The "/" character or the spaces in front or both? Thank you very much for your help.

 

[mcgrupp]

Tried it but got #N/A. Dom's formula should be just fine but thank you for responding Istvan.

 

[Domenic]

In my example, it's the "\" character that would cause it to fail. And, there are other non-alpha characters that would cause it to fail as well.

 

[István Hirsch]

Enter this formula with Ctrl + Shift +Enter:

=LEN(A1)-MATCH(TRUE,MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1)>="A",0)+1

For me it works. Did you confirm the formula with Ctrl + Shift + Enter?

Excel Workbook
	A	B
1	12a3 45 Abcde 678	13
2	23sdfgtzt456456	9
3	353 345rtzrtzASDFS7Re23	21
4	sdf45d	6
Sheet