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:\x\yz.jpg. What we want is the yz.jpg part.
Below are three approaches to this task.
The Excel-native step-by-step approach
The Excel-native solution in a single cell
Use the VBA function InStrRev in Excel
From wildcloud on July 14, 2011:
another approach:
{=RIGHT(A1, MATCH(""\"", MID(A1, LEN(A1)+1-ROW(INDIRECT(""A1:A"" & LEN(A1))), 1), 0) - 1)}
Marty R on June 23, 2012:
Every time I come back to your site I find some gold nuggets -- 3 of them this afternoon! Special thanks for the conditional formatting coupled with active cell monitoring -- took me some work to apply it to an opportunity review tool we review weekly, but it will make our process go much more smoothly with the highlighting. And for the VABInStrRev function that will simplify a routine I created in the last 10 days managing a large number of files (wish I''d come to the site sooner! :) Thank You!
The best way to understand how to build this solution is to work backwards. Suppose we could somehow replace the last reverse-slash, \ with a character that is (almost) never found in a typical string, say CHAR(255). Also suppose that this new string is in, say, cell C1. Then, we could use the MID function to get what we want with
=MID(C1,FIND(CHAR(255),C1)+1,LEN(C1))
So, how do we replace the last \ with CHAR(255)? Suppose we somehow knew the number of reverse-slashes (\s) in the string and that this number was in a cell, say, cell B1. Then, we could replace the last \ with CHAR(255) with the formula
=SUBSTITUTE(A1,"\",CHAR(255),B1)
That leaves us with having to find the number of \s in the string. We do that in cell B1 with the formula
=LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))
I like this approach because the layout is easy to understand, audit, and maintain. In addition, one can also expect to see a performance benefit since intermediate results are calculated only once.
For those who want to conserve cells (either for aesthetic reasons or because they believe there is a global shortage of good cells), it is possible to condense all of the above steps into a single cell.
=MID(SUBSTITUTE(A1,"\",CHAR(255),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))),
FIND(CHAR(255),
SUBSTITUTE(A1,"\",CHAR(255),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))
+1,
LEN(SUBSTITUTE(A1,"\",CHAR(255),LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))
One can make a small simplification in the formula by replacing the last argument to the MID function with a sufficiently large number.
=MID(SUBSTITUTE(A1,"\",CHAR(255),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))),
FIND(CHAR(255),
SUBSTITUTE(A1,"\",CHAR(255),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))
+1,
255)
The reason that the Excel-native solution gets complicated is that there is no easy way to work from the back of the string, i.e., examine the string in reverse. VBA supports the InStrRev function that finds the location of a specified token starting from the end of the string. The code below, which should be in a standard module of the workbook’s VBA project, exports the function to Excel. Then, we can use the very easy formula
=MID(A1,VBAInStrRev(A1,"\")+1,255)
Option Explicit
Function VBAInStrRev(ByVal StringCheck As String, _
ByVal StringMatch As String, _
Optional ByVal Start As Long = -1, _
Optional ByVal Compare As VbCompareMethod = vbBinaryCompare)
'vbBinaryCompare = 0 _
vbTextCompare = 1 _
vbDatabaseCompare = 2
VBAInStrRev = InStrRev(StringCheck, StringMatch, Start, Compare)
End Function
