Excel Formulas to Extract File Name From Full Path

Manually isolating file names from lengthy, nested system paths in Excel is a tedious, error-prone chore for busy analysts. While standard funding sources and IT budgets often prioritize costly database integrations to solve data-cleaning issues, mastering native Excel formulas provides an immediate, cost-effective alternative. This tactical approach grants users total autonomy over their data preparation without requiring software overhead. However, as a key stipulation, your path data must contain consistent delimiters-such as backslashes-for the formula logic to work. For example, cleanly extracting "annual_report.xlsx" from "C:\Company\Finance\annual_report.xlsx" requires targeting the very last slash. Below, we outline the exact step-by-step formulas to automate this extraction process seamlessly.

When working with large datasets, IT reports, or system exports in Excel, you often run into columns containing full file paths (for example, C:\Users\Admin\Documents\Reports\Annual_Report_2024.xlsx). While a full file path is useful for system navigation, your final reports usually only require the actual file name (e.g., Annual_Report_2024.xlsx) or even just the name without the file extension.

Depending on your version of Excel, there are several ways to tackle this task. In this comprehensive guide, we will explore the modern, simplified formulas available in Excel 365, the classic formulas compatible with older Excel versions, and alternative methods using Power Query and VBA to help you extract file names efficiently.

Method 1: The Modern Way (Excel 365 & Excel 2021+)

If you are using Microsoft 365 or Excel 2021, extracting a file name is incredibly straightforward. Excel introduced powerful text-manipulation functions that eliminate the need for long, convoluted nested formulas.

Using the TEXTAFTER Function

The TEXTAFTER function returns the text that occurs after a specific character (delimiter). Because file paths use backslashes (\) as delimiters, we want to extract everything after the very last backslash.

Here is the formula:

=TEXTAFTER(A2, "\", -1)

How It Works:

• A2: The cell containing the full file path.
• "\": The; looks for.
• -1: The instance number. A negative number tells; to search from the right side (the end) of the text string instead of the left. -1 means the last occurrence of the backslash.

For Mac users, file paths use forward slashes (/) instead of backslashes. You can adjust the formula like this:

=TEXTAFTER(A2, "/", -1)

Using TEXTSPLIT and TAKE

Another modern approach is splitting the path into an array of individual folder names and selecting the final item:

=TAKE(TEXTSPLIT(A2, "\"), , -1)

This formula splits the text by backslashes into columns and uses TAKE with -1 to grab the very last column, which is your file name.

Method 2: The Classic Formula (; 2019 and Older)

If you are building spreadsheets that need to be compatible with older versions of; (such as; 2016 or 2019), you cannot use TEXTAFTER. Instead, you must use a clever combination of legacy functions: TRIM, RIGHT, SUBSTITUTE, REPT, and LEN.

Use this classic formula:

=TRIM(RIGHT(SUBSTITUTE(A2, "\", REPT(" ", LEN(A2))), LEN(A2)))

A Step-by-Step Breakdown of How It Works:

At first glance, this formula looks like a confusing jumble of functions. However, its underlying logic is remarkably elegant. It uses a "space-padding" trick to isolate the file name. Let's break it down using the path C:\Folder\File.txt (which is 18 characters long):

1. LEN(A2): Calculates the length of the original path string. For our example, this returns 18.
2. REPT(" ", LEN(A2)): Generates a string of empty spaces equal to the length of the path. In this case, it creates a block of 18 consecutive spaces.
3. SUBSTITUTE(A2, "\", REPT(" ", LEN(A2))): Replaces every backslash (\) in your original path with that block of 18 spaces. The path now looks like this:
   C: Folder File.txt
4. RIGHT(..., LEN(A2)): Extracts the specified number of characters (18) from the far right of our newly expanded string. Because we added so many spaces, the rightmost 18 characters are guaranteed to capture only our file name (File.txt) preceded by several of those extra spaces (e.g., " File.txt").
5. TRIM(...): Cleans up the result by removing all leading and trailing spaces, leaving you with just the clean file name: File.txt.

Method 3: Extracting the File Name WITHOUT the Extension

Sometimes, extracting Report_2024.xlsx isn't enough; you might only want Report_2024. We can strip away the file extension by looking for the last period (.) in the extracted name.

The Modern; 365 Formula:

You can nest TEXTBEFORE and TEXTAFTER to pull everything after the last backslash, but before the last period:

=TEXTBEFORE(TEXTAFTER(A2, "\", -1), ".", -1)

This extracts the file name with TEXTAFTER(A2, "\", -1), and then TEXTBEFORE(..., ".", -1) isolates everything before the final period.

The Legacy; Formula:

If you are on an older version of; , you have to use a combination of MID, SEARCH, and SUBSTITUTE. Because of its extreme complexity, a common workaround is to first extract the file name with the classic formula in Column B, and then strip the extension in Column C using:

=LEFT(B2, SEARCH(".", B2) - 1)

Note: This basic legacy formula assumes there is only one period in the file name. If your file name contains multiple periods (e.g., Report.v2.xlsx), you will need a more advanced substitution formula to target only the last period.

Method 4: Using Power Query for Bulk Processing

If you import file paths regularly as part of an external data connection, you do not need to write formulas at all.; 's Power Query tool can extract file names with just a few clicks.

1. Select your table containing the file paths.
2. Go to the Data tab on the Ribbon and click From Table/Range. This opens the Power Query Editor.
3. Right-click the column header containing your file paths.
4. Navigate to Split Column > By Delimiter.
5. Select Custom as the delimiter and type a backslash (\).
6. Under "Split at", choose Right-most delimiter, then click OK.
7. Power Query will split your column. The newly created column on the right will contain your clean file names. You can rename it and click Close & Load to send the data back to your Excel worksheet.

Method 5: VBA / User-Defined Function (UDF)

If you repeatedly write these formulas across many different workbooks, you can create a custom Excel function using VBA. This allows you to simply type =GetFileName(A2) in your sheets.

To set this up:

1. Press ALT + F11 to open the VBA Editor.
2. Click Insert > Module.
3. Paste the following code into the module window:

Function GetFileName(ByVal FilePath As String) As String
    Dim pos As Integer
    pos = InStrRev(FilePath, "\")
    If pos > 0 Then
        GetFileName = Mid(FilePath, pos + 1)
    Else
        GetFileName = FilePath
    End If
End Function

4. Close the VBA Editor. Save your workbook as an Excel Macro-Enabled Workbook (.xlsm).

Now, you can use your custom function anywhere in your workbook like a standard Excel formula:

=GetFileName(A2)

Choosing the Right Method

To help you decide which approach to use, refer to the comparison table below:

By selecting the method that best matches your Excel environment and dataset size, you can instantly turn messy file paths into clean, presentation-ready lists of file names.