Summary

To remove a file extension from a file name, you can use a formula based on the LEFT and FIND functions. In the example shown, the formula in C5 is:

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

Generic formula

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

Explanation 

The core of this formula is the LEFT function which simply extracts text from the file name, starting at the left, and ending at the character before the first period (".").

=LEFT(filename,characters)

The FIND function is used to figure out how many characters to extract:

FIND(".",B5)-1

Find returns the position of the first match (6 in the first example) from which 1 is subtracted. The result, 5, goes into LEFT like this:

=LEFT(B5,5)

and the LEFT function returns the first five characters from the left: "Happy".

Note: because this formula finds the first occurrence of ".", it will remove all file extensions when there is more than one.

Related formulas

Excel formula: Remove text by position

Remove text by position

The replace function lets you replace text based on its location and length. In this case, we want to strip off the drive and path and leave only the document name. The length of this part of the string (text) is 24 and the starting position is 1, and the pattern never changes. The REPLACE function...
Excel formula: Remove first character

Remove first character

This formula uses the REPLACE function to replace the first character in a cell with an empty string (""). The arguments for REPLACE are configured as follows: old_text is the original value from column B start_num is hardcoded as the number 1 num_chars comes from column C new_text is entered as an...
Excel formula: Remove characters from right

Remove characters from right

The LEFT function is perfect for extracting characters starting from the left side of a text string. We use LEFT in this formula to extract all characters up to the number of characters we want to trim. The challenge, for values with variable length, is that we don't know exactly how many...

Related functions

Excel LEFT function

LEFT Function

The Excel LEFT function extracts a given number of characters from the left side of a supplied text string. For example, =LEFT("apple",3) returns "app".
Excel FIND function

FIND Function

The Excel FIND function returns the position (as a number) of one text string inside another. When the text is not found, FIND returns a #VALUE error.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions