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

User · Answer

Cell A1   find the position of the last slash  simple way to do it is reverse the text and then find the first slash as normal  Now you can get the length of the full text minus this number   Like so    LEN A1 -FIND     REVERSETEXT A1  1  1  This returns 21  the position of the last

User · Answer

Very late to the party  but A simple solution is using VBA to create a custom function   Add the function to VBA in the WorkBook  Worksheet  or a VBA Module  Function LastSegment S  C      LastSegment   Right S  Len S  - InStrRev S  C   End Function   Then the cell formula   lastsegment B1        in a cell and the string to be searched in cell B1 will populate the cell with the text trailing the last     from cell B1  No length limit  no obscure formulas  Only downside I can think is the need for a macro-enabled workbook   Any user VBA Function can be called this way to return a value to a cell formula  including as a parameter to a builtin Excel function    If you are going to use the function heavily you ll want to check for the case when the character is not in the string  then string is blank  etc

User · Answer

I m a little late to the party  but maybe this could help  The link in the question had a similar formula  but mine uses the IF   statement to get rid of errors   If you re not afraid of Ctrl Shift Enter  you can do pretty well with an array formula   String  in cell A1    one two three four   Formula     MAX IF MID A1 ROW  1  99  1      ROW  1  99      use Ctrl Shift Enter   Result  14  First   ROW  1  99    returns an array of integers from 1 to 99   1 2 3 4     98 99     Next   MID A1 ROW  1  99  1    returns an array of 1-length strings found in the target string  then returns blank strings after the length of the target string is reached    o   n   e          u   r                Next   IF MID I16 ROW  1  99  1      ROW  1  99     compares each item in the array to the string     and returns either the index of the character in the string or FALSE   FALSE FALSE FALSE 4 FALSE FALSE FALSE 8 FALSE FALSE FALSE FALSE FALSE 14 FALSE FALSE        Last     MAX IF MID I16 ROW  1  99  1      ROW  1  99      returns the maximum value of the array  14  Advantages of this formula is that it is short  relatively easy to understand  and doesn t require any unique characters   Disadvantages are the required use of Ctrl Shift Enter and the limitation on string length  This can be worked around with a variation shown below  but that variation uses the OFFSET   function which is a volatile  read  slow  function    Not sure what the speed of this formula is vs  others    Variations    MAX  MID A1 ROW OFFSET  A 1   LEN A1    1       ROW OFFSET  A 1   LEN A1     works the same way  but you don t have to worry about the length of the string   SMALL IF MID A1 ROW  1  99  1      ROW  1  99   2  determines the 2nd occurrence of the match   LARGE IF MID A1 ROW  1  99  1      ROW  1  99   2  determines the 2nd-to-last occurrence of the match   MAX IF MID I16 ROW  1  99  2    t  ROW  1  99    matches a 2-character string   Make sure you change the last argument of the MID   function to the number of characters in the string you wish to match

User · Answer

I think I get what you mean   Let s say for example you want the right-most   in the following string  which is stored in cell A1    Drive  Folder SubFolder Filename ext  To get the position of the last    you would use this formula    FIND     SUBSTITUTE A1          LEN A1 -LEN SUBSTITUTE A1           LEN          That tells us the right-most   is at character 24   It does this by looking for     and substituting the very last     with an       It determines the last one by using    len string -len substitute string  substring        len substring    In this scenario  the substring is simply     which has a length of 1  so you could leave off the division at the end and just use    FIND     SUBSTITUTE A1         LEN A1 -LEN SUBSTITUTE A1              Now we can use that to get the folder path    LEFT A1 FIND     SUBSTITUTE A1         LEN A1 -LEN SUBSTITUTE A1               Here s the folder path without the trailing     LEFT A1 FIND     SUBSTITUTE A1         LEN A1 -LEN SUBSTITUTE A1           -1    And to get just the filename    MID A1 FIND     SUBSTITUTE A1         LEN A1 -LEN SUBSTITUTE A1            1 LEN A1     However  here is an alternate version of getting everything to the right of the last instance of a specific character   So using our same example  this would also return the file name    TRIM RIGHT SUBSTITUTE A1     REPT     LEN A1    LEN A1

User · Answer

A simple way to do that in VBA is   YourText    c  excel text txt  xString   Mid YourText  2   Len YourText  - InStr StrReverse YourText

User · Answer

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           gt  replace     by spaces LEN   above      gt  count the chars LEN A1 -  above     gt  indicates amount of chars replaced    occurrences of      SUBSTITUTE A1     CHAR 1    above      gt  replace the Nth occurence of     by CHAR 1   Nth   amount of chars replaced   the last one  FIND CHAR 1    above      gt  Find the CHAR 1   being the last  replaced  occurance of     in our case IFERROR   above    0     gt  in case no chars were found  return  0    Hope this was helpful

User · Answer

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

User · Answer

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    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

User · Answer

In newer versions of Excel  2013 and up  flash fill might be a simple and quick solution see   Using Flash Fill in Excel

User · Answer

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

User · Answer

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

User · 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

[excel] Excel: last character/string match in a string

Examples related to excel

Examples related to excel-formula