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. IF you 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)))