An Excel formula (not VBA) for calculating the base name of a file path?

Question

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

For example, if the cell A1matters

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

formula in B1should use A1to return

file.txt

Any ideas?

+3

Karsten W. May 12 '12 at 9:03

2 answers

To get everything after the last backslash, try this formula

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

+4

barry houdini May 12 '12 at 12:04

assylias · Accepted Answer · 2012-05-12T09:14:00+0000

=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1,"\","|",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))