How to Get Filename from Path in Excel (7 Simple Methods)

Get FREE Advanced Excel Exercises with Solutions!

While working in Microsoft Excel sometimes we need to extract filename from a given path to work more effectively. This might seem difficult as the path might be of a big or small length. Today in this article, I am sharing with you how to get filename from path in Excel.


How to Get Filename from Path in Excel: 7 Easy Methods

In the following, I have shared 7 quick and easy methods to get filename from path in Excel.

Suppose we have a dataset of some File Path in our workbook. Now using functions and VBA code we will get the filename from the given path. Stay tuned!


1. Use Find and Replace Feature to Get Filename from Path

A simple technique to get the filename from the path is utilizing the find and replace feature of Excel. Using this feature you don’t have to use any type of formula to get the filename.

Steps:

  • First, select cells (B5:B11) from the worksheet with paths.
  • Second, selecting cells press Ctrl+H from the keyboard.

Use Find and Replace Feature to Get Filename from Path in Excel

  • A new window will appear. From the appeared window, type “*\” in the “Find what” box and click “Replace All”.

  • Finally, you will get the filename from the path within the same column. Simple isn’t it?

Use Find and Replace Feature to Get Filename from Path in Excel

Read More: How to Copy File Names from Folder to Excel


2. Combine LEN, SUBSTITUTE, FIND, and MID Functions to Get Filename from Path

In order to, get the filename from a path you can combine the LEN, SUBSTITUTE, FIND, and MID Functions in Excel. No worries! Just apply the formula and the output will be in your hands.

Steps:

  • Presently, select a cell (C5) and write the following formula down-
=MID(B5,FIND("*",SUBSTITUTE(B5,"\","*",LEN(B5)-LEN(SUBSTITUTE(B5,"\",""))))+1,LEN(B5))

Combine LEN, SUBSTITUTE, FIND, and MID Functions to Get Filename from Path in Excel

Formula Breakdown:
  • LEN(B5): Here, the LEN function returns the total length which is 34.
  • LEN(SUBSTITUTE(B5,”\”,””): In this part, the SUBSTITUTE function substitutes this “\” character with blanks resulting in “C:UsersPublicPicturesDeer baby”. Hence, the LEN function returns the total length which is 30.
  • SUBSTITUTE(B5,”\”,”*”,34-30): In this argument, the SUBSTITUTE function returns the character substituting with “*”.
  • FIND(“*”,C:\Users\Public\Pictures*Deer baby): In here, the FIND function will find the position of the character(*) displaying an output of 25 meaning it’s in the 25th position.
  • =MID(B5,25+1, LEN(B5): In this final part, the MID function extracts 30 characters from the string, starting from the 25th position. Thus the result stands as “Deer baby”.
  • Simply, press Enter and drag down the “fill handle” to fill all the cells.
  • In conclusion, we have successfully got the filename from the path in Excel.
  • Combine LEN, SUBSTITUTE, FIND, and MID Functions to Get Filename from Path in Excel

    Read More: How to Rename Files in a Folder in Excel


    3. Combine REPT, SUBSTITUTE, RIGHT, and TRIM Functions to Get Filename from Path

    If you want you can also apply the REPT, SUBSTITUTE, RIGHT, and TRIM functions to extract the filename from the path in Excel.

    Steps:

    • To start with, choose a cell (C5) and write the following formula down-
    =TRIM(RIGHT(SUBSTITUTE(B5,"\",REPT(" ",100)),99))

    Combine REPT, SUBSTITUTE, RIGHT, and TRIM Functions to Get Filename from Path in Excel

    • Now, hit Enter and pull the “fill handle” to get the result in all cells.


    4. Combine CHOOSECOLS and TEXTSPLIT Functions to Get Filename from Path

    Again, combining the CHOOSECOLS and TEXTSPLIT functions can also help you extract filenames from the path in Excel.

    Steps:

    • To begin with, choose a cell (C5) and write the following formula down-
    =CHOOSECOLS(TEXTSPLIT(B5,"\"),-1)

    Formula Combining CHOOSECOLS and TEXTSPLIT Functions to Get the File Name from Path

    • Now, hit Enter and pull the “fill handle” to get the result in all cells.

    File Names from Path Combining CHOOSECOLS and TEXTSPLIT Functions


    5. Merge SUBSTITUTE, LEN, CHAR, FIND, MID, and IFERROR Functions to Get Filename Without Extension from Path

    Well, in some cases you will find format names inside a path. But you might need to extract only the file name without the formats. In that case, by combining the SUBSTITUTE, LEN, CHAR, FIND, MID, and IFERROR functions you can quickly get only the filename in Excel.

    Suppose we have a dataset of some File Path with Formats in their file name. Now we are going to extract only the file name from the whole path.

    Perform SUBSTITUTE, LEN, CHAR, FIND, MID, and IFERROR Functions to Get Filename Without Extension from Path in Excel

    Steps:

    • Similarly, choose a cell (C5) to apply the formula-
    =IFERROR(MID(B5,FIND(CHAR(1),SUBSTITUTE(B5,"\",CHAR(1),LEN(B5)-LEN(SUBSTITUTE(B5,"\",""))))+1,FIND(CHAR(1),SUBSTITUTE(B5,".",CHAR(1),LEN(B5)-LEN(SUBSTITUTE(B5,".",""))))-FIND(CHAR(1),SUBSTITUTE(B5,"\",CHAR(1),LEN(B5)-LEN(SUBSTITUTE(B5,"\",""))))-1),"")

    Perform SUBSTITUTE, LEN, CHAR, FIND, MID, and IFERROR Functions to Get Filename Without Extension from Path in Excel

    • Just click the Enter key and then drag the “fill handle” down.
    • Within a moment, your precious output will be in your hands.


    6. Create a User Defined Function to Get Filename from Path

    VBA (Visual Basic for Applications) is used to customize and automate tasks in Excel. Using VBA code with your own user-defined function you can collect the filename. Follow the steps below-

    Steps:

    • While in the worksheet, press Alt+F11 to open the “Microsoft Visual Basic for Applications”.

    Create a User Defined Function to Get Filename from Path

    • In the new window, click the “Module” option from the “Insert” feature.

    • Next, in the module put the following code-
    Function GetFileName(FullPath As String) As String
    Dim List As Variant
    List = VBA.Split(FullPath, "\")
    GetFileName = List(UBound(List, 1))
    End Function

    Create a User Defined Function to Get Filename from Path

    • Without moving here and there just save the code and minimize the VBA window.
    • After that, choose a cell (C5), type your defined function within the cell, and put the cell (B5) inside the brackets of the defined function-
    =GetFileName(B5)

    • Thereafter, press Enter and drag down the “fill handle” to get the output inside the new column.

    Create a User Defined Function to Get Filename from Path


    7. Apply VBA Code to Get Filename from Path

    If you feel the user-defined function is a little bit difficult to get the filename then you can use the below method to get the filename by directly applying VBA code in Excel.

    Steps:

    • In the same fashion, press Alt+F11 to open the “Microsoft Visual Basic for Applications” window.

    Apply VBA Code to Get Filename from Path in Excel

    • Next, select “Module” from the “Insert” option.

    • Now, inside the new module write the following code down and press the “Run” icon from the top ribbon-
    Sub filePath()
    Dim filename As String
    Dim x As Variant
    For Each cell In ActiveSheet.Range("B5:B11")
    x = Split(cell.Value, Application.PathSeparator)
    filename = x(UBound(x))
    cell.Value = filename
    Next cell
    End Sub

    Apply VBA Code to Get Filename from Path in Excel

    • Finally, you will get only the filename from the path in your Excel worksheet.

    Apply VBA Code to Get Filename from Path in Excel


    Things to Remember

    In method 7, I have chosen a range (B5:B11) as I am applying the code for those cells. If you are applying the same VBA code don’t forget to change the cell range.


    Download Practice Workbook

    Download this practice workbook to exercise while you are reading this article.


    Conclusion

    In this article, I have tried to cover all the methods to get filename from path in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. Stay tuned and keep learning.


    << Go Back to Excel File Name | Excel Files | Learn Excel

    What is ExcelDemy?

    ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
    Kawser Ahmed
    Kawser Ahmed

    Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

    2 Comments
    1. or you could just use =CHOOSECOLS(TEXTSPLIT(a1,””),-1)

      • Hi Liam,
        Thank you for taking the time to comment on our blog and for suggesting a shorter method for the task that is demonstrated. We appreciate your input and always welcome new ideas.

        After carefully considering your formula, I noticed there was a backslash “\” missing from the col_delimiter argument of the TEXTSPLIT() function inside the formula. To ensure the best results, modify your formula like this: =CHOOSECOLS(TEXTSPLIT(A1,"\"),-1) (Assuming the full path address is inside the cell A1).

        We value your engagement and encourage you to keep sharing your thoughts and ideas. It’s through constructive discussions like this that we can all learn and improve. Thanks again for your comment!
        Regards,
        ExcelDemy.

    Leave a reply

    Advanced Excel Exercises with Solutions PDF

     

     

    ExcelDemy
    Logo