How to Remove the Last 3 Characters in Excel (4 Formulas)

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 Formulas to Remove the Last 3 Characters in Excel

We will be using sample employee info. list as a dataset to demonstrate all the methods throughout the article. At first, let’s have a sneak peek of it:

delete last 3 characters in excel dataset

So, without having any further discussion, let’s dive straight into all the methods one by one.


1. Use LEFT and LEN Functions to Delete 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 having any delay, let’s go through the method by steps:

🔗 Steps:

First of all, select cell E5 to store the formula result.

Then, type the formula

=LEFT(D5,LEN(D5)-3)
within the cell.

After that press the ENTER button.

Use LEFT and LEN Functions to Delete the Last 3 Characters in Excel

Finally, drag the Fill Handle icon to the end of the Given Name column.

usage of fill handle to remove the last 3 characters in excel

That’s it.

␥  Formula Breakdown:

📌 Syntax: LEFT(text, [num_chars])

📌 Syntax: LEN(text)

  • 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”.

Read more: Remove Last Character from String Excel


2. Delete the Last 3 Characters Using the REPLACE Function 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, "")
within the cell.

After that press the ENTER button.

Delete the Last 3 Characters Using the REPLACE Function in Excel

Finally, drag the Fill Handle icon to the end of the Given Name column.

That’s it.

␥  Formula Breakdown:

📌 Syntax: REPLACE(old_text, start_num, num_chars, new_text)

📌 Syntax: FIND(find_text, within_text, [start_num])

  • "" 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 characters.
  • " "  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”.

Read more: Remove Last Character from String in Excel with VBA


3. Ignore the Last 3 Characters in Excel Using the Flash Fill Feature

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 button.

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.

Ignore the Last 3 Characters in Excel Using the Flash Fill Feature

Thus, we made the Given Name column complete with only the given names of the employees.

Read more: How to Remove Characters in Excel


4. Remove the Last 3 Characters Using VBA Code 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:

Select the range (D5:D14) of the cells to remove the last 3 characters.

Press ALT + F11 keys to open the VBA window.

Go to  Insert Module.

Copy the following VBA code:

Public Function RemoveLast3Characters(rng As String, cnt As Long)

RemoveLast3Characters = Left(rng, Len(rng) - cnt)

End Function

Press CTRL + V to paste the above VBA code.

Remove the Last 3 Characters Using VBA Code in Excel

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)
within the cell.

❾  After that press the ENTER button.

Finally, drag the Fill Handle icon to the end of the Given Name column.

That’s it.

📓 Note:

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. That’s it.


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.


Read More

Mrinmoy

Hi! This is Mrinmoy. I write about Excel and VBA stuff for Exceldemy. I have my graduation completed from Khulna University of Engineering & Technology, Bangladesh. I have an immense interest in Datascience and Machine Learning. I like to play guitar and watch Tedtalks in my leisure time. I used to be an indie filmmaker and a wedding photographer. I'm liberal to any kind of constructive criticism and inclined towards expanding my learnings.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo