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, “.”
Explanation: How this Formula works
In the example we’ll use the formula below:
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.
Evaluate the nested formula
The FIND function works on to calculate the numbers of characters that we need to extract.
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.
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:
Extract file extension – TextRight
The TextRight function – based on the previously mentioned example – has two arguments:
The function strip the file extension from a filename. Useful.
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.