Extract the last token in a cell

There are times when one wants to extract the last part of a string, say the file name from a string that contains the filename including the path. This short note describes a few ways to do that.

The example we will use is the following. Cell A1 contains the value c:xyz.jpg. What we want is the yz.jpg part.

This note describes three approaches to this task.

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0131%20Extract%20last%20token.shtml

Tushar Mehta

Posted in Uncategorized

13 thoughts on “Extract the last token in a cell

  1. Here is a slightly shorter one-cell formula to retrieve the text after the LAST token (assumed to be the backslash for this example)…

    =TRIM(RIGHT(SUBSTITUTE(A1,””,REPT(” “,999)),999))

    where the 999 needs to be a number larger than the maximum number of characters in A1. Obviously, we can use LEN(A1) in place of the 999 if we want to introduce the two extra function calls.

  2. Rick, That is shorter. {grin} I would stay away from using the space character since it could be part of the last token and use something like CHAR(255). The formula would then become:

    By email, wildcloud shared the array formula

  3. @Tushar,

    The only time the formula I posted would fail with space is if there were multiple internal spaces (they would be collapsed down to single spaces) or trailing spaces (they would be lost) in the text after the last token… is that what you were referring to? If so, then your modification to what I posted would be the way to go; however, I would use CHAR(1) instead of CHAR(255) in order to make the formula four characters shorter.

  4. Otherwise just choose Replace…
    Find What: *
    Replace with: Nothing
    on a copy of the cells.

  5. Otherwise just choose Replace…
    Find What: *
    Replace with: Nothing
    on a copy of the cells.

  6. Do you have an idea how to write a formula in excel to give me the data at the intersection of material (row 1) and gauge (random #) value that is “= or greater than column ‘a’ and = or less than column b”? the answer should be the corresponding value at the intersection of the proper row for gauge, and proper column of material. Note 2 of the material are an exact match, all non-exact matches belong to the “other” material column. There are about 250 rows of additional data not included with the attached example. I prefer to do this in excel formulas, 1 or a few ; however I really want to avoid using VBA code.

  7. If you’re working with extremely long strings, the TRIM(RIGHT(…)) approach could run into the 32,767 character limit. Unlikely, but possible.

    As for VBA solutions, best to use a reference to VBScript.dll and make use of its regular expression class. This falls under the category of using the most appropriate tool for the task.

    Function ree(s As String, p As String, _
     Optional ra As Boolean = False, _
     Optional rev As Boolean = False _
    ) As Variant
     Dim re As New RegExp, mc As MatchCollection
      Dim rv As Variant, i As Long, k As Long

      re.Global = True
      re.Pattern = p
      Set mc = re.Execute(s)

      If mc.Count = 0 Then
        ree = CVErr(xlErrNull)
      ElseIf Not ra Or mc.Count = 1 Then
        ree = mc(IIf(rev, mc.Count – 1, 0)).Value
        ReDim rv(1 To mc.Count)
        k = IIf(rev, mc.Count, 1)
        For i = 1 To mc.Count
          rv(i) = mc(Abs(i – k)).Value
        Next i
        ree = rv
      End If

      Set mc = Nothing
      Set re = Nothing

    End Function

    Get last token delimited by backslashes: =ree(A1,”[^\]+$”) or =ree(A1,”[^\]+”,0,1)

    Get 4th such token from start of string: =INDEX(ree(A1, “[^\]+”,1),4)

    Get 5th such token from end of string: =INDEX(ree(A1, “[^\]+”,1,1),5)

    Since VBA functions are fairly slow, another option is finding a copy of Laurent Longre’s MOREFUNC.XLL, installing it and using its WMID function. IF you can use add-ins, this is probably the best solution.

  8. @fzz,

    What about a non-RegExp UDF…

    Function GetField(TextIn As String, Delimiter As String, FieldNumber As Long, _
                      Optional FromFront As Boolean = True) As Variant
      Dim Fields() As String
      Fields = Split(TextIn, Delimiter)
      If FromFront Then
        GetField = Fields(FieldNumber – 1)
        GetField = Fields(UBound(Fields) – FieldNumber + 1)
      End If
    End Function

    TextIn and Delimiter are obvious… FieldNumber is the delimited field number you want to retrieve and FromFront tells the UDF whether to retrieve that delimited field from the front (the default) or from the back of TextIn.

  9. Regular expressions are more general, e.g., the last word (sequence of letters only) in a string, and words could be separated by a variety of other characters. But if not regular expressions, MOREFUNC’s WMID is better than rolling your own VBA alternative.

  10. @fzz,

    I won’t argue that functions using RegExp can be made more general, usually with less code, that VB equivalents, but when did “last word” enter into the discussion? I thought the thrust of this blog article was finding the text after the last specified delimiter (slash, space, dash, etc.). My UDF generalized this goal to finding the text between the specified numbered delimiter and the delimiter following it (or the end of text if the last delimiter is specified) where the user can choose to count the delimiters from the beginning or the end of the text. At the time, I thought that was the main goal of the code you posted as well, but now I see you went for more functionality than that.

  11. Function last1(c01 As String, c02 As String)
      last1 = Join(Filter(Split(c01, c02), “.”), “”)
    End Function


    Function last2(c01 As String, c02 As String)
      last2 = Split(c01, c02)(ubound(split(c01,c02)))
    End Function
  12. A slight variation on wildclouds approach

    {=MID(A1,MAX((MID(A1,ROW(INDIRECT(“1:” & LEN(A1))),1)=””)*RIJ(INDIRECT(“1:” & LEN(A1))))+1,200)}

    Notice that row(indirect(“1:” & len(a1))) is equivalent to row(indirect(“A1:A” & len(a1)))

Leave a Reply

Your email address will not be published. Required fields are marked *