Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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.

Overview Image


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.

Sample Data Set

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.

Combining LEFT and LEN Functions to Remove the Last 3 Characters in Excel

Formula Breakdown
  • 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.

Showing Results for Combining LEFT and LEN Functions

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.

Using REPLACE Function to Delete the Last 3 Characters

Formula Breakdown
  • “” ▶ 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.

Showing Results for Using REPLACE Function to Delete the Last 3 Characters in Excel

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.

Employing Flash Fill Feature to Remove the Last 3 Characters

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

Showing Results for Employing Flash Fill Feature to Remove the Last 3 Characters in Excel

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.

Selecting desired column to Applyi VBA Code for Removing the Last 3 Characters

  • Secondly, press ALT + F11 keys ▶ to open the VBA.
  • Then, go to  Insert ▶ Module.

Opening New Module to apply VBA Code for Removing the Last 3 Characters

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

Applying VBA Code for Removing the Last 3 Characters in Excel

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

Applying formula with VBA code to remove last 3 characters in Excel

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

Showing Results for Applying VBA Code


Notes

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.


Read More

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo