Extract filename from a path in Excel
This tutorial explains how to apply formulas to extract filename with or without extension from a file path in a specific cell in Excel.
Extract filename with extension from a path
Extract filename without extension from a path
Extract filename with extension from a path
As the below screenshot shown, there is a list containing different file paths, and you want to extract only the filenames with extensions from them, the following formula will help you solve the problem.
Generic formula
=MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))
Arguments
A1: Represents the cell containing the file path you will extract the filename from it.
How to use this formula?
1. Select a blank cell to output the filename. In this case, I select the cell D3.
2. Enter the below formula into it and press the Enter key. Select the result cell, and then drag its Fill Handle all the way down to apply it to other cells.
=MID(B3,FIND("*",SUBSTITUTE(B3,"\","*",LEN(B3)-LEN(SUBSTITUTE(B3,"\",""))))+1,LEN(B3))
Now all filenames with extensions in specific file paths are extracted as the above screenshot shown.
Notes:
- In this formula, B3 is the cell containing the file path. Please change it as you need.
- If the filename is no more than 99 characters long, the below shorter formula can also be used to solve the problem. See the below screenshot shown.
=TRIM(RIGHT(SUBSTITUTE(A2,"\",REPT(" ",100)),99))
How this formula works?
=MID(B3,FIND("*",SUBSTITUTE(B3,"\","*",LEN(B3)-LEN(SUBSTITUTE(B3,"\",""))))+1,LEN(B3))
1. FIND("*",SUBSTITUTE(B3,"\","*",LEN(B3)-LEN(SUBSTITUTE(B3,"\",""))))
- LEN(B3): The LEN function calculates the total length in "D:\\files\work\Q1\Jan\text.txt" and returns the result as 30;
- LEN(SUBSTITUTE(B3,"\","")): The SUBSTITUTE function replaces the character "\" with nothing. The result is "D:filesworkQ1Jantext.txt". And then the LEN function calculates the total length of "D:filesworkQ1Jantext.txt" and get the result 24;
- SUBSTITUTE(B3,"\","*",30-24): The SUBSTITUTE function replace the third character (30-24=6) "\" in "D:\\files\work\Q1\Jan\text.txt" with a single character "*". Here the result is "D:\\files\work\Q1\Jan*text.txt";
- FIND("*","D:\\files\work\Q1\Jan*text.txt"): The FIND function locates the position of character "*" in "D:\\files\work\Q1\Jan*text.txt" and finally returns 22. Here means that the character "*" is in the 22th position in "D:\\files\work\Q1\Jan*text.txt".
2. =MID(B3,22+1,LEN(B3))
- As the above explanation shown, LEN(B3) returns the result as 30, here the MID function can be shown as =MID("D:\\files\work\Q1\Jan\text.txt",23,30). It means that the MID function extracts 30 characters from the string "D:\\files\work\Q1\Jan\text.txt", starting at the 23rd character. Here the result is text.txt.
Extract filename without extension from a path
Another circumstance, you may just need to extract the filename without extension from a path as the below screenshot shown. This section will provide a long formula to help you get it down.
Generic formula
=IFERROR(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))-FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))-1),"")
Arguments
A1: Represents the cell containing the file path you will extract the filename from it.
CHAR(1): The CHAR(1) here can be replaced with any CHAR number as you need. Or it can be replaced with a specific symbol enclosed in quotation marks such as "*".
How to use this formula?
1. Select a blank cell to output the filename without the extension.
2. Enter the below formula into it and press the Enter key. Select the result cell, and then drag its Fill Handle all the way down to apply it to other cells.
=IFERROR(MID(B4,FIND(CHAR(1),SUBSTITUTE(B4,"\",CHAR(1),LEN(B4)-LEN(SUBSTITUTE(B4,"\",""))))+1,FIND(CHAR(1),SUBSTITUTE(B4,".",CHAR(1),LEN(B4)-LEN(SUBSTITUTE(B4,".",""))))-FIND(CHAR(1),SUBSTITUTE(B4,"\",CHAR(1),LEN(B4)-LEN(SUBSTITUTE(B4,"\",""))))-1),"")
Note: In this formula, B3 is the cell containing the file path. Please change it as you need.
Related functions
Excel MID function
The Excel MID function is used to find and return a specific number of characters from the middle of given text string.
Excel FIND function
The Excel FIND function is used to find a string within another string, and returns the starting position of the string inside another one.
Excel SUBSTITUTE function
The Excel SUBSTITUTE function replaces text or characters within a text string with another text or characters.
Excel LEN function
The Excel LEN function returns the number of characters in a text string.
Excel IFERROR function
The IFERROR function is used to return a custom result when a formula evaluates an error, and return a normal result when no error is occurred.
Related formulas
Extract Multiple Lines From A Cell
If you have a list of text strings which are separated by line breaks (that occurs by pressing Alt + Enter keys when entering the text), and want to extract these lines of text into multiple cells, the formula in this tutorial will do you a favor.
Extract Last Line Of Text From A Multi-Line Cell
To extract the last line of text from a text string which is separated by line breaks, a formula in this tutorial can help you deal with this task in Excel.
Extract Nth Word From Text String In Excel
This article explains how to use a formula to extract the specific nth word from a text string in a cell in Excel.
Extract Last Two Words From A Cell In Excel
This tutorial explains how to use formula to extract the last two words from a cell in Excel.
Extract Word Beginning With A Specific Character In Excel
This tutorial provides a formula with detail steps to help you extract the word which begins with a specific character from a text string in a cell in Excel.
Extract All Words But First Or Last
In this tutorial, you will learn how to use formulas to extract all words from a cell except the first or last one in Excel.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
