Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extract text from string | Excel Worksheet Functions | |||
Extract text from a string | Excel Worksheet Functions | |||
Extract text string using MID | Excel Worksheet Functions | |||
Extract text from String | Excel Worksheet Functions | |||
Extract % from text string | Excel Worksheet Functions |