Remove file extension from filename

In this tutorial, we’ll show you how to remove file extension from a filename using custom Excel functions. We’ll use the LEFT and FIND function for data cleansing purposes.

Formula to remove file extension from filename

In this case, the separator (delimiter) is a special character, “.”

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

Explanation: How this Formula works

In the example we’ll use the formula below:

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

What is the essence of the above-mentioned formula?

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.

Formula to remove file extension from filename

Evaluate the nested formula

=LEFT(full_name, characters)

The FIND function works on to calculate the numbers of characters that we need to extract.

=FIND(".",A2)-1

The formula gets the position of the first match. In the example, 7. We have to subtract 1 because the comma is not necessary.

The result, 6; we’ll use this number as a parameter of the LEFT formula.

=LEFT(B5,6)

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

The TextLeft and TextRight function

If you are in data cleansing, it is worth to use custom Excel functions. 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 in calculations.

Remove file extension – TextLeft

Let us see the function and the arguments:

=TextLeft(cell_reference, delimiter)
The TextLeft and TextRight function

Extract file extension – TextRight

The TextRight function – based on the previously mentioned example – has two arguments:

=TextRight(cell_reference, delimiter)

The function strip the file extension from a filename. Useful.

Extract the file extension from filename

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

Tip: If you have constant delimiter, you can use the built-in Text to Columns function.

Related Formulas and Examples