Filename extract from Filepath Text String

[DaveyC]

Hi

I am trying to find a simple formulaic way of extracting the filename from a
filepath. I have looked at the Text related functions in Excel and they all
appear inapplicable due to the variable nature of filepaths i.e. the number
of sub-folders and the variation in naming of all elements. As an example
see below:

C:\Documents and Settings\Local Settings\My Local
Data\Graduation\campusmap.pdf

From the above I would want to extract just: campusmap.pdf

I have tried to locate the position of the final \ and use that as the key
to extract the filename but I am struggling to define this despite reading
all related posts on the subject. Any help very much appreciated.

Best regards
Dave

 

[Ron Rosenfeld]

Hi

I am trying to find a simple formulaic way of extracting the filename from a
filepath. I have looked at the Text related functions in Excel and they all
appear inapplicable due to the variable nature of filepaths i.e. the number
of sub-folders and the variation in naming of all elements. As an example
see below:

C:\Documents and Settings\Local Settings\My Local
Data\Graduation\campusmap.pdf

From the above I would want to extract just: campusmap.pdf

I have tried to locate the position of the final \ and use that as the key
to extract the filename but I am struggling to define this despite reading
all related posts on the subject. Any help very much appreciated.

Best regards
Dave

with your full path in A1:

=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),255)

will return everything after the last "\"

--ron

 

[Mike H]

With your path in a1 you could try

=MID(SUBSTITUTE(A1,"\","^",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))),FIND("^",SUBSTITUTE(A1,"\","^",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,256)

Mike

 

[DaveyC]

Ron
Many thanks for this, not yet sure how it works but I'll go through it and
suss it out.
Thanks again
Dave

 

[Ron Rosenfeld]

Ron
Many thanks for this, not yet sure how it works but I'll go through it and
suss it out.
Thanks again
Dave

You're welcome. Thanks for the feedback.

To "suss it out", start from the middle and work outwards.


--ron