Khan Muhammad Kawser
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.
- 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?
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))
- 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”.
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))
- 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.
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.
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”.
- 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) 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.
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.
- 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 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.
