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.

 

[Rick Rothstein]

For your second question, here's a guess... Assuming that A2 contains the text string, and B2 contains the selected sub-string, try...

=(SUM(IF(ABS((CODE(MID(SUBSTITUTE(UPPER(A2),UPPER(B2),"",1),ROW(INDEX(A:A,1):INDEX(A:A,LEN(SUBSTITUTE(UPPER(A2),UPPER(B2),"",1)))),1))-77.5))<=12.5,1))>0)+0

...confirmed with CONTROL+SHIFT+ENTER. Note that the formula will return 1 when alpha characters exist, other than the selected sub-string. Otherwise the formula returns 0.

Here is another array-entered** formula (based on the one you posted in Message #15) which seems to return the same values as your above formula...

=0+ISNUMBER(MATCH(2,1/(ABS((CODE(UPPER(MID(SUBSTITUTE(UPPER(A2),UPPER(B2),"",1),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1)))-77.5))<=12.5)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[István Hirsch]

Another version, confirm with Ctrl + Shift + Enter:

=0+ISNUMBER(MATCH(TRUE,MID(SUBSTITUTE(UPPER(A2),UPPER(B2),""),ROW(INDIRECT("1:"&LEN(A2))),1)>="A",0))

 

[Rick Rothstein]

Another version, confirm with Ctrl + Shift + Enter:

=0+ISNUMBER(MATCH(TRUE,MID(SUBSTITUTE(UPPER(A2),UPPER(B2),""),ROW(INDIRECT("1:"&LEN(A2))),1)>="A",0))

That looks like a good one! Only change I would suggest is to remove the Volatile INDIRECT function call in favor of the non-Volatile INDEX function construction that I suggested in Message #13...

=0+ISNUMBER(MATCH(TRUE,MID(SUBSTITUTE(UPPER(A2),UPPER(B2),""),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1)>="A",0))

 

[István Hirsch]

That looks like a good one! Only change I would suggest is to remove the Volatile INDIRECT function call in favor of the non-Volatile INDEX function construction that I suggested in Message #13...

=0+ISNUMBER(MATCH(TRUE,MID(SUBSTITUTE(UPPER(A2),UPPER(B2),""),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1)>="A",0))

Thanks Rick.

I have tried earlier (with another formula) to compare the speed of the two versions and failed to show that the „index” formula is faster.

Now I have tried to compare them again: 1000 cells each were filled with a text of 1350 characters, B2 had a word from the middle of the string. With the „index” formula I measured (in msec?) 1.68, 1.33, 1.30 compared with 0.79, 0.78 and 0.79 measured with the „indirect” formula. Double checked to get 1.30, 1.29, 1.30 compared with 0.78, 0.80, 0.80 resp., the indirect formula was the faster again.

 

[Domenic]

Here is another array-entered** formula (based on the one you posted in Message #15) which seems to return the same values as your above formula...

=0+ISNUMBER(MATCH(2,1/(ABS((CODE(UPPER(MID(SUBSTITUTE(UPPER(A2),UPPER(B2),"",1),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1)))-77.5))<=12.5)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

I see that you removed the second SUBSTITUTE function from the formula. Yeah, you don't really need it, but it does create a larger sized array. And I see that you added an UPPER function (the first one). Actually, this isn't really needed since the MID function is already being handed a text string having all capital letters. Also, the division (1/) is probably not very efficient. So maybe...

=ISNUMBER(MATCH(TRUE,ABS((CODE(MID(SUBSTITUTE(UPPER(A2),UPPER(B2),"",1),ROW(INDEX(A:A,1):INDEX(A:A,LEN(SUBSTITUTE(UPPER(A2),UPPER(B2),"",1)))),1))-77.5))<=12.5,0))+0

...confirmed with CONTROL+SHIFT+ENTER.

However, the formula offered by István is probably more efficient.

 

[Rick Rothstein]

Thanks Rick.

I have tried earlier (with another formula) to compare the speed of the two versions and failed to show that the „index” formula is faster.

Now I have tried to compare them again: 1000 cells each were filled with a text of 1350 characters, B2 had a word from the middle of the string. With the „index” formula I measured (in msec?) 1.68, 1.33, 1.30 compared with 0.79, 0.78 and 0.79 measured with the „indirect” formula. Double checked to get 1.30, 1.29, 1.30 compared with 0.78, 0.80, 0.80 resp., the indirect formula was the faster again.

That is surprising as I was led to believe the INDEX function is suppose to be one of Excel's faster functions, but no matter, the problem with Volatile functions is that Excel recalculates them whenever the sheet recalculates no matter what the cause, even if that change did not involve the Volatile formula nor any of its precedent references, whereas non-Volatile formulas are recalculated only if they or their precedent references change. It is not the speed of an individual formula calculation why Volatile functions should be avoided, but rather the drag on overall efficiency all of those repeated, "unneeded" calculations produce is why Volatile functions should be avoided. Here is probably a lot more about Volatile functions than you probably would ever want to know...

Volatile Excel Functions -Decision Models

 

[mfexcel]

here's my attempt:

=AGGREGATE(14,6,SEARCH({"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"},A1,ROW(INDIRECT("1:"&LEN(A1)))),1)

since it's about searching for alpha in the string, I have "hard-code" it in the formula

@rick, I agree with your opinion about INDIRECT although I am using it here. :P

 

[István Hirsch]

That is surprising as I was led to believe the INDEX function is suppose to be one of Excel's faster functions, but no matter, the problem with Volatile functions is that Excel recalculates them whenever the sheet recalculates no matter what the cause, even if that change did not involve the Volatile formula nor any of its precedent references, whereas non-Volatile formulas are recalculated only if they or their precedent references change. It is not the speed of an individual formula calculation why Volatile functions should be avoided, but rather the drag on overall efficiency all of those repeated, "unneeded" calculations produce is why Volatile functions should be avoided. Here is probably a lot more about Volatile functions than you probably would ever want to know...

Volatile Excel Functions -Decision Models

Thanks Rick, I understand the difference now.

 

[mcgrupp]

For your second question, here's a guess... Assuming that A2 contains the text string, and B2 contains the selected sub-string, try...

=(SUM(IF(ABS((CODE(MID(SUBSTITUTE(UPPER(A2),UPPER(B2),"",1),ROW(INDEX(A:A,1):INDEX(A:A,LEN(SUBSTITUTE(UPPER(A2),UPPER(B2),"",1)))),1))-77.5))<=12.5,1))>0)+0

...confirmed with CONTROL+SHIFT+ENTER. Note that the formula will return 1 when alpha characters exist, other than the selected sub-string. Otherwise the formula returns 0.

Hope this helps!

Hey Dom, tested it and it worked fine. Odd thing is that I was able to get the zero once when the values were the same (John Smith was in A2 and in B2) but pretty much every time I got #VALUE. I just kept playing solely in A2 and B2, retyping, even copying and pasting. If I put John Smith 2 in A2, I get a zero. But it does what it is supposed to since 1 appears if you have extra alpha characters so that's all that really matters now. Thanks for your follow up!

 

[Domenic]

Yeah, you'll get that error value only in that instance. Although the formula can be amended to trap the error, try the following formula instead..

=ISNUMBER(MATCH(TRUE,ABS((CODE(MID(SUBSTITUTE(UPPER(A2),UPPER(B2),"",1),ROW(INDEX(A:A,1):INDEX(A:A,LEN(SUBSTITUTE(UPPER(A2),UPPER(B2),"",1)))),1))-77.5))<=12.5,0))+0

...confirmed with CONTROL+SHIFT+ENTER. Also, note that in the unlikely event the name is repeated within the cell, the formula will flag it as having extra characters. So, for example, the formula will return 1 if A2 contains the following text string...

John Smith 245 John Smith

If you don't want such text strings flagged, replace each instance of...

SUBSTITUTE(UPPER(A2),UPPER(B2),"",1)

with

SUBSTITUTE(UPPER(A2),UPPER(B2),"")

Hope this helps!