Excel Formulas to Remove File Extensions from File Names

Cleaning up bulk file lists containing trailing extensions like .xlsx or .pdf is a tedious, error-prone chore for data analysts. Often, this raw information must be pristine before integration into reporting systems, such as those tracking standard funding sources. Mastering a dynamic Excel formula to trim these extensions grants immediate visual clarity and database compatibility. However, under the stipulation that extension lengths and naming conventions vary, a basic Excel function is insufficient. Below, we will break down the exact nested formulas required to automate this cleanup process flawlessly.

When working with file directories, system exports, or media assets in Microsoft Excel, you will often find yourself with a column full of complete filenames containing their extensions-such as report.xlsx, image.png, or archive.tar.gz. For presentation purposes, database mapping, or clean reporting, you frequently need to strip away those trailing extensions and keep only the base filenames.

Because file extensions vary in length (for example, .db is two characters, .docx is four, and .torrent is seven), you cannot simply use a basic text extraction formula that trims a fixed number of characters from the right. Instead, you need dynamic solutions that locate the last period in the string and truncate the text accordingly.

This comprehensive guide explores the best Excel formulas and techniques to trim file extensions from filenames, ranging from modern Microsoft 365 functions to classic formulas, Flash Fill, Power Query, and VBA.

Method 1: The Modern Excel Way (Excel 365 & 2024)

If you are using Microsoft 365, Excel for the Web, or Excel 2024, extracting the base filename is incredibly simple thanks to the TEXTBEFORE function. This function searches a text string and returns all text before a specified delimiter.

To handle filenames correctly, we must account for files that contain multiple periods (e.g., backup.2023.11.01.zip). If we search for the period from left to right, we will prematurely cut off the filename at the first period. To prevent this, we configure the formula to search from right to left.

The Formula:

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

How It Works:

• A2: The cell containing the original filename.
• ".": The delimiter we want to locate (the period).
• -1: The instance number. Passing a negative number tells Excel to start searching from the end (right side) of the text string instead of the beginning. -1 specifies the very last period in the string.

Handling Edge Cases (Files with No Extensions):

If a cell contains a folder name or a file with no extension (e.g., README), the standard TEXTBEFORE formula will return a #N/A error because it cannot find a period. You can easily safeguard against this by utilizing the optional arguments of the function:

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

In this enhanced version, we instruct the formula that if the delimiter is not found, it should simply return the original value in A2 instead of throwing an error.

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

If you are working on an older version of Excel, you do not have access to TEXTBEFORE. You must rely on a combination of legacy text functions: LEFT, FIND, LEN, and SUBSTITUTE.

The challenge here is that legacy Excel functions do not have a built-in "search from right" parameter. To find the last period, we must use a clever workaround to calculate its exact position.

The Formula:

=LEFT(A2, FIND("@", SUBSTITUTE(A2, ".", "@", LEN(A2)-LEN(SUBSTITUTE(A2, ".", ""))))-1)

Step-by-Step Breakdown:

1. Count the total number of periods: LEN(A2)-LEN(SUBSTITUTE(A2, ".", "")).
   This calculates the length of the original string and subtracts the length of the string with all periods removed. If the filename is archive.tar.gz, this returns 2.
2. Replace only the last period: SUBSTITUTE(A2, ".", "@", [occurrence]).
   Using the count from Step 1 as the occurrence argument, we replace only the last period with a unique placeholder character, such as @ (ensure you choose a character that won't appear in your filenames). This turns archive.tar.gz into archive.tar@gz.
3. Find the position of the placeholder: FIND("@", ...).
   This locates the exact numerical position of our temporary @ character.
4. Extract the text: LEFT(A2, [position]-1).
   Finally, the LEFT function grabs all characters up to, but not including, the placeholder character.

Handling Edge Cases:

Just like the modern method, this formula will return a #VALUE! error if the filename contains no periods. To bypass this, wrap the entire formula in an IFERROR block:

=IFERROR(LEFT(A2, FIND("@", SUBSTITUTE(A2, ".", "@", LEN(A2)-LEN(SUBSTITUTE(A2, ".", ""))))-1), A2)

Method 3: Flash Fill (The No-Formula, Instant Way)

If you only need to clean up a list of filenames once and do not need a dynamic formula that updates when the source data changes, Excel's Flash Fill is the fastest tool available.

How to Use Flash Fill:

1. Insert a new, empty column next to your column of filenames.
2. In the first row of your new column, manually type out the expected result. For example, if A2 contains quarterly_report.pdf, type quarterly_report in B2.
3. Press Enter to move to cell B3.
4. Press the keyboard shortcut Ctrl + E (or navigate to the Data tab on the ribbon and click Flash Fill).
5. Excel will instantly analyze the pattern and populate the entire column, accurately stripping away the extensions.

Note: If your list contains highly irregular patterns (e.g., some files have three dots, some have none), you may need to type out two or three manual examples before pressing Ctrl + E so Excel can accurately map the logic.

Method 4: Power Query (For Automated Workflows & Large Datasets)

When dealing with hundreds of thousands of rows, or when you are importing file lists directly from a folder directory, Power Query is the most robust tool for the job. It allows you to build an automated data-cleaning pipeline.

How to Trim Extensions in Power Query:

1. Select your data range and go to the Data tab, then click From Table/Range to open the Power Query Editor.
2. Right-click the header of the column containing your filenames and select Duplicate Column (this preserves your original data).
3. Select the duplicated column, go to the Transform tab, click Split Column, and select By Delimiter.
4. In the Split Column window:
   • Set the delimiter to Custom and type a period (.).
   • Select At the right-most delimiter.
5. Click OK. Power Query will split the column into two: one with the base filename and one with the extension.
6. Delete the column containing the extensions, rename your clean column, and click Close & Apply on the Home tab to load the clean data back into your spreadsheet.

Method 5: Creating a Custom VBA Function (UDF)

If you regularly work with file lists across multiple workbooks, you can write a short VBA script to create a custom, reusable formula: =REMOVEEXTENSION(text).

The VBA Code:

Function RemoveExtension(FileName As String) As String
    Dim pos As Long
    pos = InStrRev(FileName, ".")
    If pos > 0 Then
        RemoveExtension = Left(FileName, pos - 1)
    Else
        RemoveExtension = FileName
    End If
End Function

How to Install the Code:

1. Press Alt + F11 to open the VBA Editor.
2. Click Insert > Module.
3. Paste the code above into the module window.
4. Close the VBA Editor. You can now use the formula =RemoveExtension(A2) anywhere within that workbook (remember to save your workbook as an Excel Macro-Enabled Workbook .xlsm).

Comparison of Methods

Conclusion

Trimming file extensions in Excel does not have to be a tedious task of manual editing. For users running Microsoft 365, the elegant TEXTBEFORE formula is the clear winner. If you are supporting users on older versions of Excel, the classic nested formula combining LEFT, FIND, and SUBSTITUTE remains a highly dependable option. Choose the method that best matches your version of Excel and workflow demands to keep your data neat and professional.