Go Back

How to Remove the Extension From Filename in Excel

When we save a filename in an operating system like Microsoft Windows it carries the extension to identify the type of file. The file extension is a string of characters at the end of a filename, usually three or four characters long, that comes after a full stop, like “.doc”, “.xlsx” etc. Using the formula, we can remove the extension from filename in Excel to return the filename only.

Figure 1. Removing the Extension From Filename

Formula Syntax

The syntax for the formula is;

=LEFT(filename,FIND(".",filename)-1)

Where,

  • Filename – It is a cell reference or text string entered directly as an argument in the formula.
  • The LEFT function returns the specified number of characters from the start of the text string.
  • The FIND function returns the starting position of a character or one text string within another text string. It is used to determine the number of characters to extract.
  • 1 is subtracted from the position of the first match of “.” returned by the FIND function to get the exact number of characters by excluding the “.” from the count.

 

 

Note: This formula syntax finds the first match of “.”, therefore it will work perfectly where there are not more than one “.” in the filename.

Figure 2. The Syntax For The Formula

Example

We have a list of filenames that have different file extensions attached with them in column B like “.xlsx”, “.docx”, “.png” etc. and we want to remove the extension from filename returning the filename only in column C. We will use the following formula in cell C2 and drag it down to other cells in column C, such as;

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

Figure 3. The Final Preview of the Formula Result

Instant Connection to an Expert through our Excelchat Service:

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

The file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file
Solved by X. F. in 16 mins
Hi I need to activate excel file from Macro file cell reference value. B2 is formula based cell and file name created as cell reference b2. B2 cell reference file already opened but i need to activate that file. Below is the code here. i am not able to open this file. Sub Work() Dim wbook As String Dim filename As String filename = Range("B2") wbook = filename workbooks(filename).Activate End Sub please help us me to open this file.
Solved by M. H. in 28 mins
Excel cannot open the file '~$experimental design.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.
Solved by V. H. in 17 mins

Leave a Comment

avatar