Extract Filenames from Filepath in Excel

Getting filenames from a file path might seem simple initially, but it becomes quite challenging when dealing with a large number of file paths.

I recently faced this task and discovered some methods that can automate the process of extracting filenames. Today, I'll be sharing a few straightforward methods to help you extract filenames from a provided list of paths.

The image below provides a visual representation of what we'll be learning today.How to fetch filename from filepaths

Let's simplify the task and make your file management in Excel more straightforward.

Method #1: Using an Excel Formula

One of the simplest methods for extracting filenames from file paths involves using a formula. The following formula is a straightforward way to accomplish this task.

=MID(A1,FIND("*",SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))
Extract filename from filepath with a formula

Here in this formula, the only thing that you have to change is the cell reference 'A1'. While using this formula simply replace all the instances of cell position 'A1' with the cell position where you have file path present.

How the formula works

Let's break down how the formula works into three distinct parts:

  • The formula first determines the number of "\" characters present in the given string. This is achieved through the below formula:

=LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))

For example, if your file path is “C:\SomeFolder\Folder\Filename.doc,” this part would yield a value of "3".

  • Subsequently, the formula replaces the last "\" character in the input string with an asterisk ("*"). This substitution is carried out using the formula:

=SUBSTITUTE(A1,"\","*",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))

Continuing with the earlier example, this part of the formula transforms the file path to “C:\SomeFolder*FolderFilename.doc.”

  • With this adjustment, the task becomes simpler. Now, you just need to extract the portion that follows the "*" character. This is achieved easily using the MID function.

——————————————-

Method 2: Using a Macro to Get Filename from a Filepath

If you didn’t like the above formula, then I have created a macro for you. This macro simply replaces the file paths in your predefined range with their corresponding File names. To use this macro follow the below steps:

1. With your excel sheet opened, Press the keys “Alt + F11”.

2. This will open the Excel VBA Editor, after this navigate to Insert > Module.

3. Now paste the below code in the editor.

Sub ExtractfilePath()
Dim Filename As String
Dim x As Variant
For Each cl In ActiveSheet.Range("A1:A6")
x = Split(cl.Value, Application.PathSeparator)
Filename = x(UBound(x))
cl.Value = Filename
Next cl
End Sub

Macro to replace filename with corresponding paths

4. Next, change the cell range (“A1:A10”). You have to change this range with your own range that contains file paths.

5. When you are ready to run the code simply press the “F5” key and all your file paths will be replaced by corresponding filenames.

Method 3: By using Excel’s Find and Replace Functionality:

If you didn’t like either of the two approaches that I have mentioned above then don’t worry here is a much easier alternative for you. This is done by using the Microsoft Excels in-built ‘Find and Replace’ functionality. Follow the below steps to use this functionality to get the filenames from file paths.

1. First of all select the range where you have the FilePaths.

2. Next, press “Ctrl + H”, this will open a ‘Find and Replace’ dialog.

Shortcut method for extracting filenames

3. In the “Find What” textbox enter “*\” (without quotes) and keep the “Replace With” textbox empty. Click “Replace All” and all your Filepaths will be replaced by Filenames.

So, these were some of the methods for getting filenames from file paths. Do drop in your comments if you know any other methods.

About Content Studio

Thanks for reading. If you have found this article helpful show your love by sharing it with your friends & colleagues. All the tutorials on the Excel Trick are produced, reviewed, and fact-checked by a team of experts. You can check out our team here.