Nazmus Sakib
When a string contains different sets of data, we can extract every piece of information to different cells by removing a certain number of characters. Retrieving those data manually is inefficient and cumbersome. In this article, we will introduce 5 convenient ways to remove last character from string in Excel.
📁 Download Excel File
Download the following Excel workbook to realize the topic more clearly.
Learn to Remove Last Character from String in Excel with These 5 Suitable Approaches
Today we will use the following sample dataset to show how to remove last character from string in Excel.
1. Utilizing Flash Fill Faeature
In our first approach, we will use the Flash Fill feature to eliminate the last character. Perhaps, this is the easiest way to remove the last characters.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell D6 and type in the Year 2019, and press Enter.
- In cell D7, when you enter 2, you will notice that Excel is suggesting the Years.
- Next, click cell D11 to apply the suggestions.
- That’s it! We have removed the last characters from the string.
📕 Read More: 3 Ways to Remove Non-Numeric Characters from Cells in Excel
2. Combining LEFT and LEN Functions
Next, we will utilize the combination of LEFT and LEN functions to extract the Year from the ID.
⬇️⬇️ STEPS ⬇️⬇️
- First, select cell D6 and Enter the formula below:
=LEFT(C6,LEN(C6)-4)
- Later, use the Fill Handle icon from the bottom right corner of cell D6 and double-click.
- Great! We removed the last 4 characters to extract the Year from the ID.
🔨 Formula Breakdown
LEFT(C6,LEN(C6)-4)
👉 The LEN function returns the length of the string stored in cell C6.
👉 The LEFT function will remove the last 4 characters from the text present in cell C6.
3. Merging MID and LEN Functions
In our third approach, we will merge MID and LEN functions to remove last characters to find the Year.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select cell D6 and type in the following formula, and press Enter:
=MID(C6,1,LEN(C6)-4)
- Use the Fill Handle icon just like in the previous approach.
- Voila! We have removed the last 4 characters from the ID.
🔨 Formula Breakdown
MID(C6,1,LEN(C6)-4)
👉 The LEN function will return the number of characters present in cell C6. We subtracted 4 because we want the last 4 characters to be removed.
👉 The MID function will keep the first 4 characters of cell C6.
4. Coalescing REPLACE and LEN Functions
In our penultimate approach, we will coalesce REPLACE and LEN functions to strip the very last character from the string. Suppose we imported the ID from a different source and the IDs contain delimiters. We will remove those delimiters here.
⬇️⬇️ STEPS ⬇️⬇️
- To begin with, select cell D6 and Enter the formula below:
=REPLACE(C6,LEN(C6),1,””)
- Use the Fill Handle icon again.
- Done! Consequently, we have the corrected ID.
🔨 Formula Breakdown
REPLACE(C6,LEN(C6),1,””)
👉 We used cell C6 as the old text for the REPLACE function.
👉 Next, the LEN function returnee will work as the start number from where we want to replace. That will be the last character of the string.
👉 We put 1 as the number of characters since we want to replace only 1 character with an empty string.
5. Executing VBA Code
In the final approach, we will create a custom function executing a VBA code to remove the last character from the string.
⬇️⬇️ STEPS ⬇️⬇️
- First, navigate to the Developer tab and click Visual Basic.
- A Microsoft Visual Basic for Applications window will appear on the screen.
- From the Insert tab, click on Module.
- A code window will open momentarily.
- Copy and Paste the following code in the window and press Ctrl+S to save the code. We have created a function and we will use that now.
Public Function REMOVECHARS(s As String, i As Integer)
REMOVECHARS = Left(s, Len(s) - i)
End Function
- Afterward, move back to the worksheet. Select cell D6 and Enter the following formula:
=REMOVECHARS(C6,4)
- Use the Fill Handle icon just like before.
- As a result, the last 4 characters are removed.
How to Remove First and Last Character Concurrently in Excel
Now, we will learn how to remove first and last characters concurrently from a string using the MID function. We will extract the Code from the ID removing the Year and the Initials.
⬇️⬇️ STEPS ⬇️⬇️
- To start with, select cell D6 and Enter the formula below:
=MID(C6,5,LEN(C6)-6)
- Use the Fill Handle icon to apply the formula to the whole column.
- Eventually, we have the Code for each employee.
🔨 Formula Breakdown
MID(C6,5,LEN(C6)-6)
👉 The LEN function counts the number of characters in cell C6. We subtracted 6 because we will remove 6 characters in total.
👉 The MID function will return the Code in cell D6 as we wrote 5 as our starting character which is the first digit of the Code.
📕 Read More: 4 Ways to Remove Last 3 Characters in Excel
📝 Takeaways from This Article
📌 Firstly, we showed how to remove the last character from the string using the Flash Fill.
📌 Secondly, we demonstrated how to remove the last characters by utilizing the LEFT function.
📌 Thirdly, we applied the MID function to strip the last characters.
📌 Later, we implemented the combination of REPLACE and LEN functions to remove the very last character from a string.
📌 Next, we created a custom function by embedding a VBA code to remove the last character from the string.
📌 Lastly, we learned how to remove characters from both sides of a string merging MID and LEN functions.
Conclusion
That concludes the discussion for today. These are some convenient methods to remove the last character from a string in Excel. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.
Related Articles
- 7 Quick Tricks to Remove Apostrophe in Excel
- 6 Ways to Remove Blank Characters in Excel
- 3 Ways to Remove Non-Alphanumeric Characters in Excel
