CONCAT in Excel joins text; it does not remove a file-name suffix by itself. To remove the last extension, use =IFERROR(TEXTBEFORE(A2,".",-1),A2) when the filename is in A2, then use CONCAT if you also need to join the cleaned name to other text.
What CONCAT does—and what it does not do
CONCAT combines text from cells or text strings into one result. For example, with a folder path in C2 and a filename in B2, CONCAT can join them. But it has no option that means “delete the extension” or “remove the last few characters.” First transform the filename with a text formula, then combine the result if needed.
That distinction matters because a file extension is only one kind of suffix. In report.final.xlsx, the extension is .xlsx, while .final is part of the base name. Removing everything after the first period would turn the name into report and discard more than intended. The formula above removes text from the final period onward, leaving report.final.
Remove the final extension with TEXTBEFORE
Assume filenames are in A2:A100, with one filename per cell and no folder path included. In Microsoft 365 or Excel 2024, enter this formula beside the first filename and fill it down:
=IFERROR(TEXTBEFORE(A2,".",-1),A2)
TEXTBEFORE finds the text before a delimiter. Its third argument, -1, tells Excel to use the last occurrence of the period rather than the first. IFERROR returns the original cell value if there is no period, so a name such as README stays README rather than displaying an error.
report.xlsxbecomesreport.report.final.xlsxbecomesreport.final.archive.tar.gzbecomesarchive.tar, because only the last extension is removed.READMEremainsREADME.
This is usually what people mean by removing a file extension. It does not change or rename a file on disk: it creates a text result in the worksheet. If you want to rename actual files, the formula can help prepare names, but a separate file-management action is still required.
Join the cleaned name to other text
If your goal is to make a new combined string, place a prefix or folder path in C2 and the filename in B2. With a Windows folder path in C2, such as C:\Archive\, this formula joins that path to the filename after removing its final extension:
=CONCAT(C2,IFERROR(TEXTBEFORE(B2,".",-1),B2))
With C2 containing C:\Archive\ and B2 containing report.final.xlsx, the result is C:\Archive\report.final. The backslash at the end of the folder path is important if you want a separator between the folder and name. If the folder path does not end in a backslash, add one in the formula: with the same cells, use =CONCAT(C2,"\",IFERROR(TEXTBEFORE(B2,".",-1),B2)). That version will create a doubled separator if C2 already ends with one, so make the input consistent.
CONCAT can also join labels or other text. For instance, if A2 contains a filename and B2 contains a label, the result can include both the cleaned name and label. Remember that CONCAT does not automatically insert spaces, slashes, or punctuation; include any separator as a text argument. If you need a delimiter between many values, TEXTJOIN may be more convenient, but it also does not remove the extension for you.
Remove a known suffix rather than the extension
Sometimes “suffix” means a specific ending such as _old, _draft, or .bak, not the part after the final period. In that case, match the exact ending so you do not remove unrelated text. With a filename in A2, this formula removes _old only when it is at the end:
=IF(RIGHT(A2,4)="_old",LEFT(A2,LEN(A2)-4),A2)
For example, budget_old becomes budget, while old_budget stays unchanged. The number 4 is the length of _old, including the underscore. If the suffix you want to remove has a different length, update both the text being checked and the number of characters removed. This conditional approach is safer than using SUBSTITUTE across the whole cell, because SUBSTITUTE could remove matching text in the middle of a name too.
If the suffix is a known extension, the same pattern applies. With filenames in A2, this removes .xlsx only when it is the ending, and otherwise returns the original value:
=IF(RIGHT(A2,5)=".xlsx",LEFT(A2,LEN(A2)-5),A2)
This is useful when a list may contain several file types and you want to remove only one particular extension. For a mixed list where the desired rule is “remove whichever extension appears last,” use the TEXTBEFORE formula instead.
Check dotted names, hidden files, and full paths
Periods can appear in the base name, so consider what should count as the suffix before filling a formula down a large list. Removing from the last period preserves earlier periods, which is generally appropriate for names such as quarter.2.results.csv. If the cell contains a complete path, however, periods might also appear in folder names. It is better to separate the filename from its path before applying a rule intended for the filename.
Hidden files and extensionless names are another edge case. On systems that use a leading period for hidden files, a name such as .env may have no extension at all. The basic TEXTBEFORE formula interprets the first character as the final period and returns a blank string. If leading-dot names should be kept intact, use this adjusted formula; with the filename in A2, it preserves names whose first character is a period:
=IF(LEFT(A2,1)=".",A2,IFERROR(TEXTBEFORE(A2,".",-1),A2))
This rule treats every leading-dot name as extensionless. If your files use a different naming convention, define the rule around that convention rather than assuming every period marks an extension. Excel formulas examine text; they do not inspect the actual file type or confirm that an extension matches the file contents.
Fill the formula down and keep the results
To process a column, enter the formula in the adjacent row, then use the fill handle—the small square at the lower-right corner of the selected cell—to copy it down. Because A2 is a relative reference, Excel changes it to A3, A4, and so on. For a large table, convert the range to an Excel table with Ctrl+T on Windows; formulas entered in a table column generally fill through that column automatically. On Mac, use Command+T to create a table.
Formula results update when the source filename changes. If you need a fixed list of cleaned names, copy the result cells and use Home > Paste > Paste Values. That replaces formulas with their current text results. Check a few values before replacing anything, particularly if the source contains multiple extensions, hidden files, or names without extensions. Keep the original column until you have confirmed the output is right.
Version support and alternatives
TEXTBEFORE is available in Microsoft 365 and Excel 2024. If you use an older Excel release that does not include it, a practical option for a repeated task is Flash Fill: type the desired output beside a few examples, then choose Data > Flash Fill or press Ctrl+E on Windows. On Mac, Flash Fill availability and the shortcut can depend on the Excel version; use the Data tab if the command is available. Flash Fill infers a pattern from examples, so inspect its output when the source names follow more than one pattern.
For a fixed extension in an older version, the RIGHT, LEFT, and LEN formula shown above also works without TEXTBEFORE. For a large recurring import, Power Query can transform text as part of a repeatable data-cleaning process, but it is more setup than needed for a short list. CONCAT itself is available in current Excel versions, but it is optional when you only need to remove an extension: the cleaned-name formula can stand alone.
Common mistakes to avoid
- Using CONCAT alone: it joins pieces of text but does not identify or delete an extension. Nest the trimming formula inside CONCAT when both operations are needed.
- Removing at the first period: that can erase meaningful parts of names such as
report.final.xlsx. Use the last-period option when removing only the final extension. - Assuming every ending is an extension:
archive.tar.gzmay represent a two-part extension. The formula removes only.gz; decide whether that is the intended result. - Forgetting separators: CONCAT does not add a space or path slash automatically. Add it as a text argument or include it in the source cell.
- Expecting a file rename: a worksheet formula changes displayed text in a cell, not the name of the file stored in a folder.
Frequently Asked Questions
Can CONCAT remove a file extension in Excel?
No. CONCAT combines text. Use TEXTBEFORE to remove the final extension, then wrap that result in CONCAT if you need to join it to a path, label, or other value.
How do I remove only the last extension?
With a filename in A2, use =IFERROR(TEXTBEFORE(A2,".",-1),A2). It turns photo.edit.jpg into photo.edit, preserving the earlier period.
How can I remove .xlsx but leave other extensions alone?
With the filename in A2, use =IF(RIGHT(A2,5)=".xlsx",LEFT(A2,LEN(A2)-5),A2). It removes that exact ending and leaves other endings unchanged.
Why does TEXTBEFORE return an error?
It returns an error when the delimiter is missing or the requested occurrence cannot be found. Wrapping the formula in IFERROR, as shown above, returns the original filename in those cases. Check that the cell contains the filename you expect and that the period is a normal period character.
Does the formula rename files in a folder?
No. It produces a text value inside the workbook. To rename files, use a separate file-renaming process after checking the proposed names; do not treat a formula result as a change to the source file.
Related
- Tableau Export to Excel: Steps, Limits, and Options
- Python Pandas Excel to CSV: Steps, Options, and Fixes
- Python Pandas Excel to Dictionary: Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel #VALUE! Error: Why It Happens and How to Fix ItYou keep getting a #VALUE! error in Excel because a formula is receiving a value or argument it…
- Excel Cell Pull-Down Lists: Create, Edit, and Fix ThemTo create an Excel cell pull-down list in Microsoft 365 for Windows, select the cell, choose Data >…
- Excel Left and Right Mouse Buttons Not Working?If the left or right mouse button is not working in Excel, first check whether the same button…
- Excel Table Filter Not Working? Fixes to TryIf an Excel table filter is not working, click its drop-down arrow and choose Clear Filter From [column],…
- Excel Not Taking Formula? Fix the Cell or CalculationIf Excel is not taking a formula, first check whether the cell is formatted as Text: set it…
- SUMIFS Value Error? Fix #VALUE! in ExcelA SUMIFS formula that returns #VALUE! most often has sum and criteria ranges that are different sizes or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.