Learn how to remove file extensions from a filename using custom Excel functions. Also, use the LEFT and FIND functions to clean data.
Check our definitive guide if you want to learn all about Excel Formulas.
Formula to remove the file extension from the filename
In this case, we will use the “.” character as a separator (delimiter).
In the example, we’ll use the formula below:
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.
Evaluate the nested formula from the inside out:
The FIND function calculates the number of characters we need to extract.
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.
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
- cell reference: the cell that contains the text that you want to strip
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:
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.
Take a closer look at our UDF add-in, DataFX.