How to extract a file name from a path in Microsoft Excel

by  About
       

Path & File name: c:\My Documents\ExcelTip.xls

The Function:

=MID(A25,FIND(“*”,SUBSTITUTE(A25,”\”,”*”,LEN(A25)-LEN(SUBSTITUTE(A25,”\”,”"))))+1,LEN(A25))

Result: ExcelTip.xls



15 thoughts on “How to extract a file name from a path in Microsoft Excel

  1. “This tip is excellent to extract the file name from the path. How does one extract the names of the folders?
    I have a set of paths in Column A like C:\WINDOWS\Desktop\Countries\States\Cities\File.txt I need to extract the folder name “”Countries”" into column B, “”States”" into column C, “”Cities”" into column D and so on. How is this done?”

  2. “Kelly, you will have to use a combination of VBA code and Excel’s text to columns function to achieve what you want. Firstly, elaborate on the 3000 folders. Are they all at a single level, or do some or all have sub folders too?
    Secondly, will you need the names of the files in the folders as well? And thirdly, what do you intend to do with the folder names once they are in excel? “

  3. “OK, here is the actual situation. I have digitized approximately 4,000 music CDs. So, there are about 3,000 ‘main’ folders. Some artists…say, Led Zeppelin, have done many albums so each album name is under a single Led Zeppelin folder. Imagine entries that look like this:
    C:\My Music\Led Zeppelin\Houses of the Holy\Song 1
    C:\My Music\Led Zeppelin\Greatest Hits\Song 1
    What I want is an easy to way to share the contents of my music library with others. A spreadsheet is a good way. From there, I could export the data to a nice HTML file and post it to my server.
    At a minimum, I want to show every artist…and if there are multiple albums attributed to a particular artist then I would like to show that as well. I do not necessarily need to list every song in each album. Just artist and album name would be fine.”

  4. “In a new workbook, go to the VBA editor by hitting Alt+F11.
    Next, select the current sheet in the project explorer on the left. Click Insert>Module.
    Copy and paste the following code:
    Sub IndexFiles()
    With Application.FileSearch
    .LookIn = “”C:\My Music”"
    .FileType = msoFileTypeAllFiles
    .SearchSubFolders = True
    .Execute
    End With
    End Sub
    Type in the correct directory path you want to evaluate in the .LookIn line.( I have typed in “”C:\My Music”" already).
    Now close the VBA editor, and save the workbook.
    Select cell A1 on the worksheet.
    On the excel macro toolbar, click Run>Macro. You will see the name “”Indexfiles”" in the box that pops up.
    Select it and run the macro. Be patient as it might take a while to complete since you say there are 3000 folders.
    When it completes, you will have a full path listing of all files and folders in you main music directory.
    The next part you can do yourself:
    Use the Data>Text to Columns feature (on column A) to separate the names of the folders and files into individual cells. Once that is done you can delete any columns you dont want, format and export as you desire.
    P.S Thanks to Stephen Hoadley of mrexcel.com for the giving me the code for a similar requirement.
    thank you!
    Kelly Smith Posted on: 31-12-1969
    Cool…thanks Vikram!”

  5. “Hi Vikram / Kelly,

    this looked very useful, so I tried it myself.
    However, when I ran it (using Excel 2000) it didn’t appear to do anything.
    It paused for a second, and then returned me to the blank worksheet, with nothing listed.I changed the path and triple checked that it is correct, and there are plenty of files there to find.
    Did it work for anyone else?
    Vikram – Do you have any ideas what may be stopping it working for me?
    Thanks,
    Alan.”

  6. “Try this code. Should work without a problem:
    ============================
    Sub IndexFiles()
    With Application.FileSearch
    .LookIn = “”C:\MyMusic”"
    .FileType = msoFileTypeAllFiles
    .SearchSubFolders = True
    .Execute
    End With
    cnt = Application.FileSearch.FoundFiles.Count
    For i = 1 To cnt
    Rng = “”A”" & i
    Range(Rng).Value = Application.FileSearch.FoundFiles.Item(i)
    Next i
    End Sub “

  7. “The HYPERLINK function doesn’t work correctly in a conditional statement. For example:IF(B8=”"Y”",HYPERLINK(D8,C8),C8)
    If true a link should be created but if false no link should exist….doesn’t work this way…it always creates a link.”

  8. “This tip is excellent to extract the file name from the path. How does one extract the names of the folders?

    I have a set of paths in Column A like
    C:\WINDOWS\Desktop\Countries\States\Cities\File.txt

    I need to extract the folder name “”Countries”" into column B, “”States”" into column C, “”Cities”" into column D and so on. How is this done?”

  9. “Kelly, you will have to use a combination of VBA code and Excel’s text to columns function to achieve what you want. Firstly, elaborate on the 3000 folders. Are they all at a single level, or do some or all have sub folders too?
    Secondly, will you need the names of the files in the folders as well? And thirdly, what do you intend to do with the folder names once they are in excel? “

  10. “OK, here is the actual situation. I have digitized approximately 4,000 music CDs. So, there are about 3,000 ‘main’ folders. Some artists…say, Led Zeppelin, have done many albums so each album name is under a single Led Zeppelin folder. Imagine entries that look like this:

    C:\My Music\Led Zeppelin\Houses of the Holy\Song 1
    C:\My Music\Led Zeppelin\Greatest Hits\Song 1

    What I want is an easy to way to share the contents of my music library with others. A spreadsheet is a good way. From there, I could export the data to a nice HTML file and post it to my server.

    At a minimum, I want to show every artist…and if there are multiple albums attributed to a particular artist then I would like to show that as well. I do not necessarily need to list every song in each album. Just artist and album name would be fine.”

  11. “In a new workbook, go to the VBA editor by hitting Alt+F11.
    Next, select the current sheet in the project explorer on the left. Click Insert>Module.
    Copy and paste the following code:
    =================================
    Sub IndexFiles()
    With Application.FileSearch
    .LookIn = “”C:\My Music”"
    .FileType = msoFileTypeAllFiles
    .SearchSubFolders = True
    .Execute
    End With
    End Sub
    ================================
    Type in the correct directory path you want to evaluate in the .LookIn line.( I have typed in “”C:\My Music”" already).

    Now close the VBA editor, and save the workbook.
    Select cell A1 on the worksheet.
    On the excel macro toolbar, click Run>Macro. You will see the name “”Indexfiles”" in the box that pops up.

    Select it and run the macro. Be patient as it might take a while to complete since you say there are 3000 folders.

    When it completes, you will have a full path listing of all files and folders in you main music directory.

    The next part you can do yourself:
    Use the Data>Text to Columns feature (on column A) to separate the names of the folders and files into individual cells. Once that is done you can delete any columns you dont want, format and export as you desire.

    P.S Thanks to Stephen Hoadley of mrexcel.com for the giving me the code for a similar requirement.”

  12. “this looked very useful, so I tried it myself.

    However, when I ran it (using Excel 2000) it didn’t appear to do anything.

    It paused for a second, and then returned me to the blank worksheet, with nothing listed.

    I changed the path and triple checked that it is correct, and there are plenty of files there to find.

    Did it work for anyone else?

    Vikram – Do you have any ideas what may be stopping it working for me?

    Thanks, “

  13. “Try this code. Should work without a problem:
    ============================
    Sub IndexFiles()
    With Application.FileSearch
    .LookIn = “”C:\MyMusic”"
    .FileType = msoFileTypeAllFiles
    .SearchSubFolders = True
    .Execute
    End With
    cnt = Application.FileSearch.FoundFiles.Count
    For i = 1 To cnt
    Rng = “”A”" & i
    Range(Rng).Value = Application.FileSearch.FoundFiles.Item(i)
    Next i
    End Sub
    =============================”

Leave a Reply

Your email address will not be published. Required fields are marked *


9 + = seventeen

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>