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.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in