If we want to find whether or not a character or string occurs within another string we can use FIND or SEARCH. But these functions only tell us if a string (or character) exist in another string, they don't tell us if the string we are looking for occurs multiple times.
For example, we have a path\filename like so:
c:\excel\reports\march.xlsx
and we want to extract the filename. To do this we have to find the position of the last \ and extract everything to the right.
FIND and SEARCH will both give the result 3, the position of the first \, so we have to use a trick involving SUBSTITUTE to find the last one.
If we know the length of the string,
LEN("c:\excel\reports\march.xlsx") = 27
and we know the length of the string after all the \ are removed,
LEN("c:excelreportsmarch.xlsx") = 24
then we know how many \ are in the string, in this case 27 - 24 = 3.
To remove all of the \ use SUBSTITUTE to replace \ with an empty string "" (our string is in A1)
SUBSTITUTE(A1,"\","")
So
LEN(SUBSTITUTE(A1,"\","")) = 24
We can call SUBSTITUTE using it's fourth argument to tell it to replace the last \ with another character which we will use later. I'm using CHAR(9) which is a character I know won't appear in the path to my file.
SUBSTITUTE(A1,"\",CHAR(9),LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))
Where A1 contains our string and
LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))
tells SUBSTITUTE to replace the 3rd \
I can now use SEARCH or FIND to give me the position of my CHAR(9) character.
SEARCH(CHAR(9),SUBSTITUTE(A1,"\",CHAR(9),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))
Knowing this I can use RIGHT to extract the filename. We can work out the number of characters for RIGHT to extract by subtracting the position of CHAR(9) from the length of the string.
RIGHT(A1,LEN(A1)-SEARCH(CHAR(9),SUBSTITUTE(A1,"\",CHAR(9),LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))
If we want the path rather than the filename, we can use LEFT.
LEFT(A1,SEARCH(CHAR(9),SUBSTITUTE(A1,"\",CHAR(9),LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))
Flash Fill
You can achieve the same results using Flash Fill, but Flash Fill does not automatically update results.
If your source data changes, you'll need to re-run Flash Fill, whereas a formula will automatically update.
InstrRev - VBA Function
If you want to use VBA, you can achieve everything in this post using InstrRev which is one of a number of useful VBA string functions.
Download Example Workbook
Enter your email address below to download the sample workbook.
Excel PivotTable Field List Tips
sam
Is it possible to solve this with LAMBA ?
Mynda Treacy
Yes, anything you can do with an existing function can be done with a LAMBDA, but there’s not much point in writing your own function when there’s an existing solution available with built in functions.
David N
I personally prefer to use REPLACE instead of RIGHT to isolate the end of a string because it avoids the need for any “length minus find” math games. And the formula turns out to be one character shorter in this case, so it’s a virtual break-even on typing.
=REPLACE(A1,1,SEARCH(CHAR(9), SUBSTITUTE(A1,”\”,CHAR(9),LEN(A1)- LEN(SUBSTITUTE(A1,”\”,””)))),””)
Duncan Williamson
Alternatively, use Flash Fill …
Philip Treacy
Thanks Duncan, yes that is true. Unfortunately FF does not automatically update when the source data changes. I’ve added a note to the post to make this clear.
Thanks
Phil
Diane Smith
Too bad that Excel doesn’t have the function InStrRev that Microsoft Access has.
Then the formulas could be:
FileName: Right(A1,Len(A1)-InStrRev(A1,”\”))
PathName Left(A1,InStrRev(A1,”\”)-1)
much simpler
Philip Treacy
Hi Diane,
Yes it would be nice to have this in the sheet, but you can use InStrRev with VBA.
VBA String Functions
Cheers
Phil