Lamisa Musharrat
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.
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, “”)
🔨 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.
- Drag this icon downward, and you will see that all the other cells are now filled.
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.
- 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
- 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)
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.
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.
- 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.
- 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.
- 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.
- Therefore, you will see that you have filled the column.
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)
🔨 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.
- 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)))
🔨 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
- 3 Ways to Remove Non-Numeric Characters from Cells in Excel
- 7 Quick Tricks to Remove Apostrophe in Excel
- 3 Ways to Remove Non-Alphanumeric Characters in Excel
