4 Ways to Remove Last 3 Characters in Excel

Suppose you want to remove the last 3 characters of data in Excel. It is easy to do manually when you have little data. But when you have a huge amount of data, doing this manually will not be a wise thing because Excel has tons of formulas to help you out in this type of situation. In this article, we are going to discuss how to remove the last three characters in Excel.


πŸ“ Download Excel File

Download the Excel file we used to create this article so you can practice.


Learn to Remove Last 3 Characters in Excel with These 4 Suitable Approaches

You can use different functions and Excel VBA codes to remove the last 3 characters in Excel. Here we are presenting four unique examples, using which you can easily remove the last three characters in Excel.

The dataset that we are going to use contains the Product ID and Types of Tea in a Tea shop. Suppose we want to remove the word tea, which is composed of three characters, and assign the rest of the name to another column.


Approach

1. Using REPLACE Function

You can use the REPLACE function combined with theΒ FIND function. Follow the steps below to learn.

 ⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, select the cell where you want to see the output. We have selected cell D6.
  • Secondly, in that selected cell, type the formula given below.

=REPLACE(C6, FIND(” β€œ,C6)+1, 3, β€œβ€)

Using the REPLACE Function to Remove Last 3 Characters in Excel

πŸ”¨ Formula Breakdown

REPLACE(C6, FIND(” β€œ,C6)+1, 3, β€œβ€)

πŸ‘‰Β  In Excel, β€œβ€ stands for a null string.

πŸ‘‰Β  The final 3 characters of a text line are referred to as 3.

πŸ‘‰Β  FIND(” β€œ,D5)+1 locates the first character of the final three characters.

πŸ‘‰Β  ” β€œ is used to indicate when a text line has ended.

πŸ‘‰Β  So, finally, the text β€œGreen Tea” is truncated by REPLACE(C6, FIND(” β€œ,C6)+1, 3, β€œβ€), which removes the last three characters, β€œTea.”

  • Thirdly, press Enter from the keyboard. And so, you can see the result in your preferred cell.
  • Finally, it’s time to fill the other cells. You don’t have to enter the formula for every other cell and can automatically fill the rest of the cells, using the Fill Handle icon.

Applying the REPLACE Function to Remove Last 3 Characters

  • Drag this icon downward, and you will see that all the other cells are now filled.


Approach

2. Executing VBA Code

You can write VBA code and make your own function; this type of function is called a user-defined function. Let’s see how to write VBA code in Excel and create a function that will remove the last 3 characters from a dataset.

Follow the steps below.

 ⬇️⬇️ STEPS ⬇️⬇️

  • To begin with, go to the toolbar, and from there, click on the Developer tab.
  • Then, from the Code group, you will find Visual Basic, click on that.

  • A window will open named Microsoft Visual Basic for Application.
  • From there, click on the Insert and then click on the Module You have opened one module.

Using the VBA code to Remove Last 3 Characters in Excel

  • You will paste the code given below in this module.
Public Function Remove_Last_3_Characters(rng As String, cnt As Long)
Remove_Last_3_Characters = Left(rng, Len(rng) - cnt)
End Function
  • By inserting the VBA code we have defined a new function, which is Remove_Last_3_Characters

Using the VBA code to Remove Last 3 Characters in Excel

  • Now, from your keyboard, press Ctrl+SΒ to save this module.
  • Then, after saving this module, you have to go back to your worksheet, and in the cell where you want to see the output, you will have to insert the following formula. Here, we have used cell D6 to assign the formula.

=Remove_Last_3_Characters(C6,3)

Using the VBA code to Remove Last 3 Characters in Excel

Here, C6 is the cell location of the data from which we are going to remove three characters.

3 is the cnt from the VBA code, which is counting how many characters we have to remove.

  • Finally, you have to use the Fill Handle icon to fill the other cells of the column.

Using the VBA code to Remove Last 3 Characters


Approach

3. Applying Flash Fill Feature

Flash Fill is a feature of Excel which enables us to fill a list or column automatically if it is already inserted somewhere in that worksheet. So we can easily use this to create a list where you want to remove the last three characters. To know what you have to do, follow the steps mentioned below.

 ⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, select the cell where you want to see the list. We have selected cell D6.
  • After that Enter the first data only in the first cell. For example, here we have inserted only Green in cell D6 instead of Green Tea.

Using the Flash Fill to Remove Last 3 Characters

  • In the next cell of the same column type only the first letter of the data. Here, we have only typed B for Black and see that the entire list has appeared.

Applying the Flash Fill to Remove Last 3 Characters in Excel

  • After that, just click on the list and it will fill the unfilled cell of the column. So here, the cell is filled automatically because of the Flash Fill feature of Excel.

Utilizing the Flash Fill to Remove Last 3 Characters in Excel

  • Alternatively, you can also do this by keyboard command, so in your second step, you have to select the entire column.
  • After that, press Ctrl+E from your keyboard.

Employing the Flash Fill to Remove Last 3 Characters

  • Therefore, you will see that you have filled the column.

Using the Flash Fill to Remove Last 3 Characters in Excel


Approach

4. Combining LEFT and LEN Functions

You can also try combining functions such as the LEFT and LEN functions of Excel. The LEFT function of Excel returns characters of a text string based on the specified numbers. On the other hand, the LEN function returns the number of characters in a specified text string. Let’s combine these two functions to remove three characters from Excel data.

 ⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, select the cell where you want to see the list. We have selected cell D6.
  • After that, in that selected cell, insert the formula given below.

=LEFT(C6,LEN(C6)-3)

Using the LEFT ad LEN functions to Remove Last 3 Characters in Excel

πŸ”¨ Formula Breakdown

LEFT(C6,LEN(C6)-3)

πŸ‘‰Β  Here, C6 stands for the text that we want to remove characters from, and LEN(C6)-3 denotes the desired character removal.

πŸ‘‰Β  As a result, LEN(C6)-3 subtracts 3 from the length of the text β€œGreen Tea” after computing its length.

πŸ‘‰Β  C6 is the cell number for the text β€œGreen Tea.”

πŸ‘‰Β  LEFT(D5,LEN(D5)-3) truncates the final three characters of the text β€œGreen Tea,” i.e. β€œTea.”

  • Then, from the keyboard, press Enter to enter this formula, and you will see the result in your selected cell.
  • Finally, your work isn’t over. You have to fill the other cells of the column, too. You can do this easily using the Fill Handle icon.

Applying the LEFT ad LEN functions to Remove Last 3 Characters

  • Just drag this icon downward, and you will see that you have filled all the other cells.

πŸ“• Read More: 5 Ways to Remove Last Character from String in Excel


How to Remove Characters from Left in Excel

Maybe sometimes you need to remove all characters from the left side of a value. You can also do this in a very easy manner just by using Excel functions. But what functions you will use? Well, there are many functions and combinations that can achieve this task, but in this article, we are going to demonstrate only one method. In this method, we will use a combination of four functions.

These functions are the RIGHT function, the LEN function, the TRIM function, and the FIND function. Suppose now we want to know what the product category is in our previous dataset. To know this, we will use a function that will be a combination of these functions.

Follow the procedure below to see how to use these functions in this case.

 ⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, specify the cell where you want to see the list. Here, we have selected cell D6.
  • Secondly, you have to insert the formula below.

=RIGHT(TRIM(C7),LEN(TRIM(C7))-FIND(” β€œ,TRIM(C7)))

Remove Characters from Left in Excel

πŸ”¨ Formula Breakdown

RIGHT(TRIM(C7),LEN(TRIM(C7))-FIND(” β€œ,TRIM(C7)))

πŸ‘‰Β  Here, C7 is the cell number where the characters that we want to work with are stored.

πŸ‘‰Β  To start, we located the space character in the cell using the FIND function.

πŸ‘‰Β  The space between Green and Tea is considered aΒ  character, which appears in the name in cell C7 at the fourth position, so FIND(” β€œ,TRIM(C7))) would return 4.

πŸ‘‰Β  In order to determine the total number of characters in the cell after the space character, the value returned by the FIND function was subtracted using the LEN function.

πŸ‘‰Β  After that, I used the RIGHT function to extract it because I now knew how many characters to extract from the text string’s right.

πŸ‘‰Β  Also, to make sure that any leading, trailing, or double spaces are addressed, I used the TRIM function.

  • Thirdly, from the keyboard, press Enter to enter this formula, and you will see the result in your selected cell.
  • Finally, you have to fill the other cells of the column as well, and you can do this easily using the Fill Handle icon.

  • Drag this icon downward, and you will see that you have filled all the other cells.

πŸ“• Read More: 6 Ways to Remove Blank Characters in Excel


πŸ“ Takeaway from This Article

πŸ“Œ You have learned how to use VBA code in Excel to build a user-defined function.

πŸ“ŒΒ  You can now use different functions i.e LEN, FIND, REPLACE, TRIM, etc, and their combinations to remove three characters from data.


Conclusion

Removing characters from a dataset is quite an easy task until you have to deal with huge data, so to work more smartly and efficiently, you can follow the processes illustrated above. Please leave a comment if you have any suggestions or questions. Don’t forget to visit our Excelden page to enhance your Excel-related knowledge.


Related Articles

(Visited 55 times, 1 visits today)
Lamisa Musharrat

Lamisa Musharrat

Hey there! I am Lamisa Musharrat, a Marine Engineer and a Content Developer. I love to help people by writing articles for them. Hope you will enjoy my blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo