Filename extract from Filepath Text String
 

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

Filename extract from Filepath Text String
 

On Thu, 20 Dec 2007 07:07:01 -0800, DaveyC
wrote:

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

Filename extract from Filepath Text String
 

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" wrote:

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

Filename extract from Filepath Text String
 

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" wrote:

On Thu, 20 Dec 2007 07:07:01 -0800, DaveyC
wrote:

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

Filename extract from Filepath Text String
 

On Thu, 20 Dec 2007 07:25:02 -0800, DaveyC
wrote:

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