Maruf Islam
It is not uncommon to find additional data attached to the right of entries in some cases. It is very important for us to get rid of them. However, doing it manually is a nightmare. In this article, we describe 5 useful methods to remove the last character from a string in Excel. In the following figure, we eliminate the final character from the string in Excel with the aid of the REPLACE function.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the whole process.
5 Easy Ways to Remove Last Character from String in Excel
Here, we will demonstrate 5 suitable ways to remove the last character from string Excel. We use functions such as REPLACE, LEFT, LEN, VALUE as well as VBA Macro Code. We also use Flash Fill to remove the last character in case numbers follow a pattern. Now, consider we have a dataset where entries should have been like Employee Details but present as Erroneous Name.
This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.
1. Use REPLACE Function to Remove Last Character from String
The REPLACE function replaces specified parts of the text string with a newly assigned text string. Let’s walk through the following steps to do the task.
📌 Steps:
- First, type the formula in any blank cell (C5):
=REPLACE(B5,9,1," ")
Here, B5 is the old_text reference. We have the text “Jane Doe*” in cell B5. We want the text as “Jane Doe”. So, we want a space starting character start_num “9” (i.e after Jane Doe). We want only one character (*) to replace, so num_chars is “1”. And the new_text will be a space (” “).
- Next, press ENTER. The data in the cell appear as below.
- Now, repeat steps 1 and 2 with individual start_num and num_chars.
- Therefore, we will get a picture similar to the picture below.
Read more: How to Remove Characters in Excel
2. Combine LEFT and LEN Functions in Excel
In order to remove characters from the right side of the string, LEN and LEFT functions are handy to use. Let’s go over the steps to complete the task step by step.
📌 Steps:
- First, select any blank cell (C5). Paste the formula
=LEFT(B5, LEN(B5) - 1)
In the formula, The LEN function subtracts the declared number of characters (i.e.1) from a string length (B5) and the LEFT function displays the rest of the characters from the beginning of the string length.
- Next, press ENTER.
- Therefore, it replaces the last character in Erroneous Name.
- Afterward, drag the Fill Handle and the rest of the Erroneous Names look similar to the image below.
Read more: Remove First Character from String in Excel
3. Embedding VBA Macro Code to Delete Last Character
We can use a custom function to remove the last character from a string. It’s quite data-friendly, as the control of this function stays in the user’s hand. We can write a VBA Macro Code to generate a function like this.
=RemoveLC = Left(str, Len(str) – num_chars)
Then you apply the function as a formula in the dataset similar to Method 2.
📌 Steps:
- First, press ALT+F11 altogether to open up Microsoft Visual Basic.
- In the Microsoft Visual Basic window, go to the Toolbar >> Insert >> Module.
- Now, paste the below VBA Macro Code in the Module.
Function RemoveLC(str As String, num_chars As Long)
RemoveLC = Left(str, Len(str) - num_chars)
End Function- Then, go to the worksheet and Type
[email protected](B5,1)
- We can see, VBA Macro Code creates a custom function. In the formula, B5 is the str, and 1 is the number of characters you want to subtract from the text string.
- Then, press ENTER.
- Therefore, the formula removes the last character like in the picture below.
- Next, drag the Fill Handle and the rest of the Erroneous Names get the shape depicted in the image below.
Read more: How to Remove the First Character from a String in Excel with VBA
4. Combine Excel VALUE, LEFT and LEN Functions
Sometimes numbers are followed by unnecessary characters. In that case, it is obvious we just want the values. To achieve this, we can just combine the previous Method 2 with the VALUE function.
📌 Steps:
- First, insert the following formula in any blank cell (C5).
=VALUE(LEFT(B5,LEN(B5)-1))
- Next, hit ENTER, it removes the last unnecessary character.
- Next, drag the Fill Handle then you will come across a data shape similar to the picture below.
5. Remove Last Character from String Through Flash Fill Option
Often the data are not more than numbers following a pattern. In that case, if the numbers are followed by unnecessary characters, we can simply remove them via the Flash Fill.
📌 Steps:
- First, click on the adjacent cell (C5) of an entry in the dataset.
- Next, type the 1st entry without the unnecessary character.
- Then, press ENTER, and then go to Data Tab >> Flash Fill (in the Data Tools section).
- Now, click on the Flash Fill option. All the number values will appear, omitting the characters.
Conclusion
In the article, we use REPLACE, LEN, and LEFT functions as well as VBA Macro Code to remove the last character from any string. REPLACE function removes characters from specific positions. The LEFT function combined with the LEN function does it by subtracting the last character and showing the rest characters in a string. VBA Macro Code generates a custom function to remove the last character. Hope you find the above-explained methods worthy of your quest. Comment, if you need further clarifications and have something to add. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!
