How to quickly extract filename from full path in Excel?
Supposing you have a worksheet which contains a list of file paths, now you would like to extract just the filename (the right part of the final backslash) from each path as following screenshots shown. Are there any quick tricks to deal with this task?
Extract filename from full path with formula in Excel
Extract filename from full path with User Defined Function
Extract filename from full path with VBA code
Extract filename from full path with formula in Excel
In Excel, you can use the below formula to quickly extract only file name from the full path.
Select a blank cell, enter the below formula into it and then press the Enter key.
=MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))
Tips: A1 is the cell that you want to extract the filename from), and press Enter button, then drag the fill handle to fill range you want. After that, the filename is extracted from each cell.
Extract filename from full path with User Defined Function
With the following User Defined Function, you can get the filename easily and quickly.
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
Function FunctionGetFileName(FullPath As String) As String
'Update 20140210
Dim splitList As Variant
splitList = VBA.Split(FullPath, "\")
FunctionGetFileName = splitList(UBound(splitList, 1))
End Function
3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window and go back to the worksheet. In a blank cell, B1 for instance, enter the below formula into it and press the Enter key.
=FunctionGetFileName(A1)
Then select cell B1 again, drag the fill handle to the range that you want to apply this formula, and all the filenames have been extracted from the full paths as following result:
Extract filename from full path with VBA code
Besides the User Defined Function, a VBA code also can help you to extract the filenames. Please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications Window.
2. Click Insert > Module, and paste the following code in the Module Window.
Sub GetFileName()
'Update 20140210
Dim Rng As Range
Dim WorkRng As Range
Dim splitList As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
splitList = VBA.Split(Rng.Value, "\")
Rng.Value = splitList(UBound(splitList, 1))
Next
End Sub
3. Then press F5 key to run this code, and select the range that you want to extract the filename from, see screenshot:
4. And then click OK, the filenames have been extracted from the selection as follows:
Note: With this VBA code, the original data will be destroyed, so you should copy one before applying this code.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
