Remove file extension from filename

Formula to remove the file extension from the filename

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

Check our definitive guide if you want to learn all about Excel Formulas.

How to remove the file extension from the filename

Here are the steps to remove the file extension from a filename in Excel:

  1. Open Excel.
  2. Type =LEFT(original_filename, FIND(delimiter,original_filename)-1).
  3. Press Enter.
  4. The formula will return the filename without its extension.

Explanation

The formula extracts the part of the original filename that appears before the specified delimiter. Take a closer look at how it works.

  1. FIND(delimiter, original_filename): This part of the formula locates the position of the first occurrence of delimiter in original_filename. For instance, if original_filename is “file-name.csv” and the delimiter is “-“, FIND returns the position of the dash (-).
  2. Subtract 1: By subtracting 1 from the position returned by FIND, you get the position just before the delimiter. This allows you to capture everything up to but not including the delimiter.
  3. LEFT(original_filename, FIND(…) – 1): Finally, LEFT retrieves characters from the start of original_filename up to this position, effectively extracting the text before the delimiter.

Example

We will use the “.” character as a separator (delimiter) in this example.

Formula:

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

Explanation:

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

=LEFT(A2,FIND(“.”,A2)-1)

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)

extract-file-extension-using-textright-function

The function strips the file extension from a filename. Note: You can use the built-in Text to Columns function if you have a constant delimiter.