Advertisement
Advertisement


Excel: last character/string match in a string


Question

Is there an efficient way to identify the last character/string match in a string using base functions? I.e. not the last character/string of the string, but the position of a character/string's last occurrence in a string. Search and find both work left-to-right so I can't think how to apply without lengthy recursive algorithm. And this solution now seems obsolete.

2020/06/17
1
188
6/17/2020 10:22:11 PM

Accepted Answer

With newer versions of excel come new functions and thus new methods. Though it's replicable in older versions (yet I have not seen it before), when one has Excel O365 one can use:

=MATCH(2,1/(MID(A1,SEQUENCE(LEN(A1)),1)="Y"))

This can also be used to retrieve the last position of (overlapping) substrings:

=MATCH(2,1/(MID(A1,SEQUENCE(LEN(A1)),2)="YY"))

| Value  | Pattern | Formula                                        | Position |
|--------|---------|------------------------------------------------|----------|
| XYYZ   | Y       | =MATCH(2,1/(MID(A2,SEQUENCE(LEN(A2)),1)="Y"))  | 3        |
| XYYYZ  | YY      | =MATCH(2,1/(MID(A3,SEQUENCE(LEN(A3)),2)="YY")) | 3        |
| XYYYYZ | YY      | =MATCH(2,1/(MID(A4,SEQUENCE(LEN(A4)),2)="YY")) | 4        |

Whilst this both allows us to no longer use an arbitrary replacement character and it allows overlapping patterns, the "downside" is the useage of an array.


Note: You can force the same behaviour in older Excel versions through either

=MATCH(2,1/(MID(A2,ROW(A1:INDEX(A:A,LEN(A2))),1)="Y"))

Entered through CtrlShiftEnter, or using an inline INDEX to get rid of implicit intersection:

=MATCH(2,INDEX(1/(MID(A2,ROW(A1:INDEX(A:A,LEN(A2))),1)="Y"),))
2020/06/18
1
6/18/2020 5:54:24 AM


How about creating a custom function and using that in your formula? VBA has a built-in function, InStrRev, that does exactly what you're looking for.

Put this in a new module:

Function RSearch(str As String, find As String)
    RSearch = InStrRev(str, find)
End Function

And your function will look like this (assuming the original string is in B1):

=LEFT(B1,RSearch(B1,"\"))
2016/06/20

tigeravatar and Jean-Fran├žois Corbett suggested to use this formula to generate the string right of the last occurrence of the "\" character

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",LEN(A1))),LEN(A1)))

If the character used as separator is space, " ", then the formula has to be changed to:

=SUBSTITUTE(RIGHT(SUBSTITUTE(A1," ",REPT("{",LEN(A1))),LEN(A1)),"{","")

No need to mention, the "{" character can be replaced with any character that would not "normally" occur in the text to process.

2019/06/07

Just came up with this solution, no VBA needed;

Find the last occurance of "_" in my example;

=IFERROR(FIND(CHAR(1);SUBSTITUTE(A1;"_";CHAR(1);LEN(A1)-LEN(SUBSTITUTE(A1;"_";"")));0)

Explained inside out;

SUBSTITUTE(A1;"_";"") => replace "_" by spaces
LEN( *above* ) => count the chars
LEN(A1)- *above*  => indicates amount of chars replaced (= occurrences of "_")
SUBSTITUTE(A1;"_";CHAR(1); *above* ) => replace the Nth occurence of "_" by CHAR(1) (Nth = amount of chars replaced = the last one)
FIND(CHAR(1); *above* ) => Find the CHAR(1), being the last (replaced) occurance of "_" in our case
IFERROR( *above* ;"0") => in case no chars were found, return "0"

Hope this was helpful.

2017/02/14

You could use this function I created to find the last instance of a string within a string.

Sure the accepted Excel formula works, but it's much too difficult to read and use. At some point you have to break out into smaller chunks so it's maintainable. My function below is readable, but that's irrelevant because you call it in a formula using named parameters. This makes using it simple.

Public Function FindLastCharOccurence(fromText As String, searchChar As String) As Integer
Dim lastOccur As Integer
lastOccur = -1
Dim i As Integer
i = 0
For i = Len(fromText) To 1 Step -1
    If Mid(fromText, i, 1) = searchChar Then
        lastOccur = i
        Exit For
    End If
Next i

FindLastCharOccurence = lastOccur
End Function

I use it like this:

=RIGHT(A2, LEN(A2) - FindLastCharOccurence(A2, "\"))
2016/05/19

Considering a part of a Comment made by @SSilk my end goal has really been to get everything to the right of that last occurence an alternative approach with a very simple formula is to copy a column (say A) of strings and on the copy (say ColumnB) apply Find and Replace. For instance taking the example: Drive:\Folder\SubFolder\Filename.ext

Find what

This returns what remains (here Filename.ext) after the last instance of whatever character is chosen (here \) which is sometimes the objective anyway and facilitates finding the position of the last such character with a short formula such as:

=FIND(B1,A1)-1
2017/06/08