How to Get Filename from Path in Excel (6 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.


Download Practice Workbook

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


6 Easy Methods to Get Filename from Path in Excel

In the following, I have shared 6 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


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


    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 filename from path in excel.

    Steps:

    • To start with, choose a cell (C5) and writhe 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. 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, combining 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.


    5. 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, pressAlt+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) and 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


    6. 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+F11to 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 6, 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.

    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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.

    Khan Muhammad Kawser

    Khan Muhammad Kawser

    Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

    We will be happy to hear your thoughts

    Leave a reply

    Advanced Excel Exercises with Solutions PDF

     

     

    ExcelDemy
    Logo