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

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

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, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.