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

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.

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:

=SUBSTITUTE(RIGHT(SUBSTITUTE(A1,””,REPT(CHAR(255),999)),999),CHAR(255),””)

By email, wildcloud shared the array formula

=RIGHT(A1,MATCH(“”,MID(A1,LEN(A1)+1-ROW(INDIRECT(“A1:A”&LEN(A1))),1),0)-1)

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

Otherwise just choose Replace…

Find What: *

Replace with: Nothing

on a copy of the cells.

Otherwise just choose Replace…

Find What: *

Replace with: Nothing

on a copy of the cells.

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.

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.

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

Else

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.

IFyou can use add-ins, this is probably the best solution.@fzz,

What about a non-RegExp UDF…

Optional FromFront As Boolean = True) As Variant

Dim Fields() As String

Fields = Split(TextIn, Delimiter)

If FromFront Then

GetField = Fields(FieldNumber – 1)

Else

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.

Or you could just use MoreFunc’s WMID function and be done with it :)

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.

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

last1 = Join(Filter(Split(c01, c02), “.”), “”)

End Function

or

last2 = Split(c01, c02)(ubound(split(c01,c02)))

End Function

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