Remove file extension from filename

Learn how to remove file extensions from a filename using custom Excel functions. Also, use the LEFT and FIND functions to clean data.

If you want to learn all about Excel Formulas, check out our definitive guide.

Formula to remove the file extension from the filename

In this case, we will use the “.” character as a separator (delimiter).

=LEFT(original_filename, FIND(delimiter,original_filename)-1)

Explanation:

In the example, we’ll use the formula below:

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

What is the essence of the formula mentioned above?

The LEFT function extracts the text from the full name. The starting point is the first character from the left. After that, we’ll find the ending point of the last character before the second section.

The LEFT function extracts the text from the full name

Evaluate the nested formula from the inside out:

=LEFT(full_name, characters)

The FIND function calculates the number of characters we need to extract.

=FIND(".",A2)-1

The formula gets the position of the first match; 7. We have to subtract 1 because the comma is not necessary. The result 6, we’ll use this number as an argument of the LEFT formula.

=LEFT(B5,6)

The function returns the first six characters from the left: “report”.

Strip file extension using the TextLeft and TextRight

Just a few words about user-defined functions before we take a deep dive into the details:

It is worth using custom Excel functions if you are in data cleansing. In addition, it comes in handy if we have a multipurpose tool. To be fully effective, we’ll use a user-defined function to show the possibilities of VBA. As usual, this solution should cut down the time spent on calculations.

How to use the TEXTLEFT function

Syntax:

=TextLeft(cell_reference, delimiter)

Arguments:

  • cell reference: the cell that contains the text that you want to strip
  • delimiter
using textleft function to strip file extension

Extract a text part from the right using TextRight

The TextRight function – based on the previously mentioned example – has a simple syntax; it also operates two arguments:

=TextRight(cell_reference, delimiter)

The function strip the file extension from a filename. Note: If you have a constant delimiter, you can use the built-in Text to Columns function.

extract file extension using textright function

You can use the demonstrated user-defined functions: download DataFX free Excel add-in.

Stay tuned.

Istvan Vozar

Istvan is the co-founder of Visual Analytics Ltd. He writes blog posts and helps people to reach the top in Excel.