Working with text in Excel can be very interesting once you get the hold of it. You can remove extensions from filename in Excel using the LEFT and FIND functions. In this tutorial, you will learn how to remove the file extension from filename in Excel.
How to Remove Extension from Filename in Excel
You can nest a FIND function inside a LEFT function to remove the file extension from filename in Excel. The formula syntax would be.
Here, the FIND function returns the position of the character match that you provide as an argument. Find follows the syntax,
=FIND(text_to_find, text, [start_num]). All filenames are separated with a dot (“.”) from the extension. Hence, you will be providing a dot as the first argument for the FIND function. The second argument will be the filename where you will search the dot. Next, the formula will subtract 1 from the value that is returned. The resulting value will be the provided as an argument to the LEFT function which follows the syntax,
=LEFT(text, num_chars). LEFT will extract that number of characters from the filename, which as a result would remove the extension.
The following example contains a chunk of a financial analysis data set. Column A has the filenames with extensions. To remove the extensions from the filenames:
- Go to cell B2.
- Write the formula,
=LEFT(A2,FIND(".",A2)-1)in the formula bar of cell B2.
- Press Enter to apply the formula to B2.
- Drag the formula using the fill handle on the bottom right from cell B2 to B6.
This will show the filenames in column B without the extensions.
Excel offers some cool tricks working with text. Removing the extensions from filenames is one of them. With the help of the FIND and LEFT functions, you can easily remove the file extension from the filename.