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

 

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.

---

Table of Contents

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

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

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

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

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

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

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

7. Apply VBA Code to Get Filename from Path

Things to Remember

Conclusion

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.

• 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?

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))

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.

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))

• 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)

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

---

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.

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),"")

• 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”.

• 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

• 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.

---

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.

• 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

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

---

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.

Get Filename.xlsm

---

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.