There might be some cases where you may want to remove the last four characters from the dataset in Excel.
For example, if you have a list of file names with file extensions, you might want to remove the extensions, which are typically the last three characters preceded by a dot (e.g., “.txt” or “.pdf”), to work with just the file names.
Or, when dealing with sensitive information like credit card numbers, removing certain characters at the end of the string may be necessary to protect the data while preserving the relevant part.
This tutorial shows six methods for removing the last four characters in Excel.
Method #1: Using Flash Fill
Excel’s Flash Fill feature automatically applies data transformations based on pattern recognition. For instance, it can remove the last four characters of a cell value.
Suppose we have a dataset of filenames with three-character extensions preceded by dots.
We want to use Flash Fill to remove the last four characters from the file names and show the results in column B.
We use the following steps:
- Select cell B2 and enter the contents of cell A2 minus the last four characters.
- Select cell B3 and type in the contents of cell A3 minus the last four characters. You may notice that as you begin to type, Flash Fill gives you suggestions of the following entries in grey, as shown below:
If the suggestions provided by Flash Fill match your intentions, press Enter to accept them and complete the task. Otherwise, type the entry and continue to the next step.
- Select cell B4 directly beneath the two entries you previously made.
- On the “Home” tab, open the “Fill” drop-down on the “Editing” group and choose “Flash Fill.”
Alternatively, press Ctrl + E, the shortcut for Flash Fill.
The task is done, and the file names without the last four characters are displayed in column B:
Note: It is essential to double-check the results of Flash Fill because it may not always predict the pattern correctly, especially if the data is complex. In such cases, you can manually edit the suggestions in the cells where Flash Fill didn’t get it right.
Also read: How to Add Text to the Beginning or End of all Cells in Excel
Method #2: Using LEFT and LEN Functions
Suppose we have the following dataset of file names with 3-character extensions preceded by dots.
We want to use a formula combining the LEFT and LEN functions to remove the last four characters from the file names and display the results in column B.
We use the below steps:
- Select cell B2 and enter the following formula:
=LEFT(A2, LEN(A2) - 4)
- Drag or double-click the fill handle feature in cell B2 to copy the formula to other cells down the column.
The file names without extensions are displayed in column B:
Explanation of the Formula
=LEFT(A2, LEN(A2) - 4)
The breakdown of the formula is as follows:
- ‘A2’: Refers to the cell from which you want to remove the last four characters. In this case, it’s cell A2.
- ‘LEN(A2)’: The LEN function returns the text’s length (number of characters) in cell A2.
- ‘LEN(A2) – 4’: This portion subtracts 4 from the text length in cell A2. The number 4 is deducted here, meaning the formula intends to remove the last four characters from the text in cell A2.
- ‘LEFT(A2, LEN(A2) – 4)’: The LEFT function takes two arguments: the text you want to remove from (A2) and the number of characters you want to extract (LEN(A2) – 4). In this case, it pulls characters from the left side of the text in A2, starting from the first character and continuing until the length of the text minus four characters.
Note: If leading or trailing spaces exist in the original values, the formula may not produce the expected results. To avoid this, removing these spaces before applying the formula is essential. You can find instructions on removing leading and trailing spaces in this tutorial’s “How to Remove Leading and Trailing Spaces” section.
Also read: How to Remove Text after a Specific Character in Excel
Method #3: Use a Formula Combining MID and LEN Functions
Let’s say we have the following filenames with three-letter extensions that always start with a dot.
We want to use a formula combining the MID and LEN functions to remove the last four characters from the filenames and show the results in column B.
We use the below steps:
- Select cell B2 and type in the below formula:
=MID(A2,1,LEN(A2)-4)
- Double-click or drag the fill handle feature in cell B2 to copy the formula to the other cells down the column.
The filenames in column A minus the last four characters are displayed in column B:
Explanation of the Formula
=MID(A2,1,LEN(A2)-4)
This formula uses the MID function, designed to extract a part of a text string. MID stands for “middle” and requires three inputs: the text string, the starting position, and the number of characters to extract.
In extracting a substring that excludes the last four characters from the text in cell A2, the formula starts at the first character (position 1). It pulls characters from the beginning to the length of the value minus four characters. This way, the result only shows the characters before the last four characters of the original text.
Note: It’s crucial to eliminate any leading or trailing spaces from the original values before applying the formula to ensure that it yields the expected results. To learn how to do this, refer to the “How to Remove Leading and Trailing Spaces” section of this tutorial.
Also read: How to Remove Commas in Excel (from Numbers or Text String)
Method #4: Using REPLACE and LEN Functions
Let’s say we have the following filenames with three-letter extensions that always start with a dot.
We want to use a formula combining the REPLACE and LEN functions to remove the last four characters from the filenames and show the results in column B.
We use the below steps:
- Select cell B2 and type in the below formula:
=REPLACE(A2, LEN(A2) - 3, 4, "")
- Drag or double-click the fill handle in cell B2 to copy the formula to other cells down the column.
The results are displayed in column B:
Explanation of the Formula
=REPLACE(A2, LEN(A2) - 3, 4, "")
The formula in this method says: “Take the text in cell A2, and starting from the position that is three characters less than the length of the text, replace the next four characters with an empty string.”
The formula uses the REPLACE function that replaces a specified portion of text within a text string with new text. The function has the following syntax:
=REPLACE(old_text, start_num, num_chars, new_text)
Here’s what each argument in the REPLACE function does:
- ‘old_text’: This is the original text string in which you want to make the replacement. In this case, the original text is in cell A2.
- ‘start_num’: This argument specifies the starting position within the `old_text` where you want to begin the replacement. In this case, it is the integer value returned by LEN(A2) – 3.
- ‘num_chars’: This argument tells how many characters you want to replace, starting from the position specified in `start_num.` In this example, it is the value 4.
- ‘new_text’: This is the text you want to insert in place of the characters you’re replacing. In this example, the new text is an empty string.
Note: To ensure the formula obtains the expected results, removing any leading or trailing spaces from the original values is essential. To know how to do this, refer to the “How to Remove Leading and Trailing Spaces” section of this tutorial.
Also read: How to Remove a Specific Character from a String in Excel
Method #5: Using SUBSTITUTE and RIGHT Functions
Let’s say we have the following filenames with three-letter extensions that start with dots.
We want to use a formula combining the SUBSTITUTE and RIGHT functions to remove the last four characters from the filenames and show the results in column B.
We use the below steps:
- Select cell B2 and type in the below formula:
=SUBSTITUTE(A2,RIGHT(A2,4),"")
- Drag or double-click the fill handle in cell B2 to copy the formula to the other cells down the column.
The values in column A minus the last four characters are displayed in column B:
Explanation of the Formula
=SUBSTITUTE(A2,RIGHT(A2,4),"")
The formula removes the rightmost four characters from the text in cell A2 by identifying those four characters using ‘RIGHT(A2,4)’ and then replacing them with an empty string.
The formula uses the SUBSTITUTE function, whose syntax is as follows:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
Here’s what each argument in the SUBSTITUTE function does:
- ‘text’: This is the original text string where you want to replace occurrences of a particular substring. In this example, the original text string is in cell A2.
- old_text’: This is the substring within the original text you want to find and replace. In this case, the old text is identified by `RIGHT(A2,4)`.
- ‘new_text’: You want to replace the old_text with this text. In this case, the new text is an empty string.
- [instance_num] (optional): This argument allows you to specify which occurrence of the old_text you want to replace. If you omit this argument by default, Excel replaces all occurrences of old_text within the text string. If you specify an instance number, Excel will only replace that particular instance. The entire old text is replaced in this example because we omitted this argument.
Also read: How to Separate Names in Excel
Method #6: Using a VBA User-Defined Function
We can use Excel VBA to construct a User-Defined Function (UDF) and use it to remove the last four characters in Excel.
Let’s say we have the following filenames with three-letter extensions that start with dots.
We want to use a UDF to remove the last four characters from the filenames and show the results in column B.
We use the below steps:
- Press Alt + F11 to launch the Visual Basic Editor (VBE). Alternatively, on the “Developer” tab, click “Visual Basic” on the “Code” group.
- Click “Insert” on the menu bar and choose “Module” to insert a new module.
- Copy the following code and paste it into the new module:
Function REMOVELASTCHARS(str As String, num_chars As Long)
REMOVELASTCHARS = Left(str, Len(str) - num_chars)
End Function
- Save the file as an Excel Macro-Enabled Workbook (*.xlsm).
- Press Alt + F11 to switch to the active worksheet containing the dataset.
- Select cell B2 and type in the below formula:
=REMOVELASTCHARS(A2,4)
Note: Notice that as you begin to type the UDF, Excel IntelliSense shows the UDF on the list of suggested functions:
You can press the Tab key to enter the UDF.
- Double-click or drag the fill handle in cell B2 to copy the formula to other cells down the column.
The results are shown in column B:
Explanation of the VBA Code
The REMOVELASTCHARS UDF requires a string input (‘str’) and the number of characters to remove from the end (‘num_chars’). The function then generates a new string that includes all characters from the start of the original input string until the length of the input string minus the number of characters specified in `num_chars.`
Here’s an explanation of how the code works:
- ‘Left(str, Len(str) – num_chars)’: The ‘Left’ function extracts a portion of the input string ‘str.’ It takes two arguments, ‘str’ which is the original text) and ‘Len(str) – num_chars,’ which calculates the number of characters to keep from the left side of the string.
- ‘REMOVELASTCHARS = …’: This line of code assigns the result of the ‘Left’ function to the `REMOVELASTCHARS` function itself. In other words, it returns the modified string as the result of the function.
Note: To achieve the desired outcome, removing any spaces at the beginning or end of the initial values is essential before applying the formula. For step-by-step instructions on how to do this, please refer to the “How to Remove Leading and Trailing Spaces” section in this tutorial.
Also read: How to Extract Text After Space Character in Excel?
How to Remove Leading and Trailing Spaces
Extra spaces at the start or end of a text string or cell content are known as leading and trailing spaces, respectively.
They can harm the accuracy of operations and functions, particularly when handling text data.
Such spaces are usually accidental and may consist of any spaces that come before the first or after the last visible character in a cell.
Note: Spaces can be either regular or non-breaking. Common spaces separate words and can be broken across lines, while non-breaking spaces prevent word wraps or line breaks at specific positions to keep certain elements or phrases together.
All the formulas involving text functions in this tutorial will not return the expected results if the original values have leading or trailing spaces.
You must remove the leading or trailing spaces for the formulas to yield the intended results.
Identifying leading spaces is easy as the cell values start away from the cell’s right border. However, it can be challenging to spot trailing spaces.
In the below example, there are trailing regular spaces in column A, as displayed in the formula bar:
Note: The insertion point blinks some distance after the last visible character in a cell, indicating trailing spaces that are invisible.
To remove regular trailing spaces, use the TRIM function as in the example below:
The TRIM function removes all spaces from a text string, leaving only single spaces between words.
However, it cannot remove non-breaking spaces. If the TRIM function fails to remove leading or trailing spaces, it may be due to non-breaking spaces.
To remove non-breaking spaces, you must use a formula that replaces the non-breaking spaces with regular spaces before applying the TRIM function, as shown in the example below:
In the formula =TRIM(SUBSTITUTE(A2,CHAR(160),” “)), the SUBSTITUTE function replaces all non-breaking spaces (CHAR(160)) with regular spaces (a single space character).
The TRIM function is applied to the result of the SUBSTITUTE function, ensuring that any extra spaces resulting from the substitution are removed and the text is left with only the necessary spaces.
This tutorial described six methods for removing the last four characters in Excel. We hope you found the tutorial helpful.
Other articles you may also like:
