6

Is it possible to determine the basename, i.e. the last path component of a file path in Excel without defining a custom VBA function?

For example, if cell A1 has the value

C:\Some\deep\directory\structure\file.txt

the formula in B1 should use A1 to return

file.txt

Any ideas?

assylias
  • 321,522
  • 82
  • 660
  • 783
Karsten W.
  • 17,826
  • 11
  • 69
  • 103
  • 1
    Why are you opposed to using VBA? There are much more reliable methods of doing this than simply stripping out what you will be assuming is the path separator. – Cody Gray - on strike May 12 '12 at 09:15
  • Actually (almnost) duplicate of: http://stackoverflow.com/questions/350264/how-can-i-perform-a-reverse-string-search-in-excel-without-using-vba – assylias May 12 '12 at 09:16
  • @CodyGray: I believe a formula is faster; also I like the workbook VBA free so there is no user interaction (activating macros) necessary. – Karsten W. May 12 '12 at 09:26
  • @CodyGray Unfortunately, in a scenario where you have VBA functions called from a sheet, and a macro is executing, changing values on the sheet and causing the formulas to recalculate, *particularly in a loop,* Excel enters some sort of a gray area. Functions may execute properly, or they may return an error (e.g. `#VALUE`) for no reason at all (not reproducible when slowly stepping through code!); execution flow can stop unexpectedly in the middle of a routine; or Excel may crash with an invalid operation performed. I'm so fed up with that that I now avoid VBA on sheets as much as possible. – GSerg May 12 '12 at 12:52

2 Answers2

9
=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1,"\","|",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))
assylias
  • 321,522
  • 82
  • 660
  • 783
  • 1
    I notice that this doesn't work if there's a space in the file path. It's because of using spaces as the outer substitute char, but then finding spaces earlier in the string. – Doug Glancy May 12 '12 at 14:43
  • Replaces the spaces with something else: "!" for example. – assylias May 12 '12 at 14:52
  • I'm not sure at what point you're suggesting the replacement. I thought you (and @Karsten) might want to know about this shortfall in the solution. If a replacement char is used it should and illegal file name char like: ? : < > | – Doug Glancy May 12 '12 at 15:05
  • @Dougglancy I have amended accordingly. – assylias May 12 '12 at 15:30
  • 3
    aaaand for Linux paths: `=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1,"/","|",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))` – Matt Kneiser May 17 '17 at 22:37
  • it works in google docs as well as of 2021-01 – Felipe Valdes Jan 18 '21 at 23:33
6

To get everything after the last backslash try this formula

=REPLACE(A1,1,LOOKUP(2^15,FIND("\",A1,ROW(INDIRECT("1:"&LEN(A1))))),"")

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • +1 I don't know if I'll ever remember this pattern. If not, I'll at least be filled with the same childlike wonder each time I see it. – Doug Glancy May 12 '12 at 14:45
  • Beautiful. A critical part of converting HTML-link local paths for each user's filespace (ie, opening a spreadsheet looking for different local paths on each computer). Thanks. – Johnny Utahh Jun 23 '15 at 19:19