Mrinmoy Roy
It is quite obvious to all of us that we detect the presence of some unnecessary characters at the end of a text line. So, we all might want to remove all of those last unnecessary characters to present our data in a better way. Well, if you are having these issues and looking for ways to remove the last characters in Excel, then please move forward with this article. Because we will show you four distinct formulas to remove the last 3 characters in Excel with ease.
Download the Practice Workbook
You are recommended to download the Excel file and practice along with it.
4 Easy Ways to Remove the Last 3 Characters in Excel
Here, we will demonstrate how to remove the last 3 characters in Excel by combining the LEFT and LEN functions, using the REPLACE function, using the FLASH Fill feature, and applying the VBA code. We will be using sample employee info. list as a dataset to demonstrate all the methods throughout the article. First, let’s have a sneak peek at it.
So, without having any further discussion, let’s dive straight into all the methods one by one.
Method 1: Combining LEFT and LEN Functions to Remove the Last 3 Characters in Excel
In the Employee Info. List, the Full Name column holds all the full names of the employees. Here, we can see that every single name bears the same surname which is “Roy”. So, our aim is to remove the surname from all the employee names and store only the given name in the Given Name column. So, without any delay, let’s go through the method in steps:
Steps:
- First of all, select cell E5 to store the formula result.
- Then, type the formula.
=LEFT(D5,LEN(D5)-3)
- After that press the ENTER button.
- LEN(D5)-3 ▶ calculates the length of the text, “Jason Roy” and then subtracts the result with 3.
- D5 ▶ refers to the cell address of the text “Jason Roy”.
- =LEFT(D5,LEN(D5)-3) ▶ truncates the last 3 characters i.e. “Roy” from the text “Jason Roy”.
- Finally, drag the Fill Handle icon to the end of the Given Name column.
Read more: Remove Last Character from String Excel
Method 2: Using REPLACE Function to Delete the Last 3 Characters in Excel
Now, we will delete the last 3 characters by replacing them with a null string (“”). To do so, we will use the Replace function here.
Steps:
- First of all, select cell E5 to store the formula result.
- Then, type the formula.
=REPLACE(D5, FIND(" ",D5)+1, 3, "")
- After that press the ENTER button.
- “” ▶ refers to a null string in Excel.
- 3 ▶ refers to the last 3 characters in a text line.
- FIND(” “,D5)+1 ▶ finds the starting number of the last 3
- ” “ ▶ used for the detection of the end of a text line.
- =REPLACE(D5, FIND(” “,D5)+1, 3, “”) ▶ truncates the last 3 characters i.e. “Roy” from the text “Jason Roy”.
- Finally, drag the Fill Handle icon to the end of the Given Name column.
Read more: Remove Last Character from String in Excel with VBA
Method 3: Employing Flash Fill Feature to Remove the Last 3 Characters in Excel
Microsoft Excel 2013 and the later versions have come up with an extremely useful tool called Flash Fill. In this section, we will delete the last 3 characters i.e. ignore the surname, Roy from each of the employee names with the help of the Flash Fill tool. Here are the steps to follow:
Steps:
- First of all, select cell E5 and type Jason in it.
- Then press the ENTER
- In the next cell, E6 starts typing Brandon only leaving his surname, Roy.
- Now you will notice that Excel has already got the pattern and suggest a preview for all the following names.
- All you need to do is just press the ENTER button again.
- Thus, we made the Given Name column complete with only the given names of the employees.
Read more: How to Remove Characters in Excel
Method 4: Applying VBA Code to Remove the Last 3 Characters in Excel
Lastly, in this section, we will use the VBA codes to delete the last 3 characters i.e. surname name Roy from all the employees’ full names. Now here comes the steps:
Steps:
- Firstly, select the range (D5:D14) of the cells to remove the last 3 characters.
- Secondly, press ALT + F11 keys ▶ to open the VBA.
- Then, go to Insert ▶ Module.
- After that, copy the following VBA code:
Public Function RemoveLast3Characters(rng As String, cnt As Long)
RemoveLast3Characters = Left(rng, Len(rng) - cnt)
End Function- Besides, press CTRL + V from the keyboard to paste the above VBA code.
- Next, save the VBA code and go back to your spreadsheet.
- Now, select cell E5 to store the formula result.
- Then, type the formula.
=RemoveLast3Characters(D5,3)
- After that press the ENTER button.
- Finally, drag the Fill Handle icon to the end of the Given Name column.
The function =RemoveLast3Characters(Text,Number)is a synthesized function. You can use this function along with the VBA code to delete any number of last characters from a string. Just input any desired number into the Number slot of the function.
Things to Remember
📌 Be careful about the syntax of the functions.
📌 Insert the data ranges carefully into the formulas.
📌 (“”) refers NULL String in Excel.
Conclusion
To wrap up, we have discussed 4 distinct methods to remove the last three characters in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap.
