In instances, we come across additional data attached to the right of entries. We badly want to remove them. But doing it manually is a hideous task. In this article, we describe some useful methods to remove the last character from a string in excel.
Now, consider we have a dataset where entries should have been like Employee Details but present as Erroneous Name and ID.
Dataset For Download
5 Easy Ways to Remove Last Character from String Excel
Method 1: Using REPLACE Function
The REPLACE function replaces specified parts of the text string with a new assigned text string. Its syntax is
old_text; refers to any reference cell you want the text to be replaced.
start_num; declares from which number of character replacing will happen.
num_chars; defines how many characters will get replaced.
new_text; is the text that ultimately will be in the place of replaced characters.
Step 1: Type the formula in any blank cell (C4)
Here, B4 is the old_text reference. We have a text “Jane Doe*” in cell B4. 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 (” “).
Step 2: Press ENTER. The data in the cell (C4) appear as we thought of.
Step 3: Repeat Steps 1 and 2 with individual start_num and num_chars. Then we will get a picture similar to the picture below.
Read more: How to Remove Characters in Excel
Method 2: Using LEFT and LEN Function
In order to remove characters from the right side of the string, LEN and LEFT functions are handy to use. The syntax of the LEN function is
text calculates the length of any data string.
LEFT returns a specific number of characters from the beginning or left of any text string. The syntax of the LEFT function is
Step 1: Select any blank cell (C4). Paste the formula
In the formula, The LEN function subtracts the declared number of characters (i.e.1) from a string length (B4) and the LEFT function displays the rest of the characters from the beginning of the string length.
Step 2: Press ENTER. It replaces the last character in Erroneous Name.
Step 3: 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
Method 3: Using VBA Macro Code
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
Then you apply the function as a formula in the dataset similar to Method 2.
Step 1: Press ALT+F11 altogether to open up Microsoft Visual Basic.
Step 2: In Microsoft Visual Basic window, Go to the Toolbar>>Insert>>Module.
Step 3: 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
Step 4: Go to the worksheet and Type
We can see, VBA Macro Code creates a custom function. In the formula, B4 is the str, and 1 is the number of characters you want to subtract from the text string.
Step 5: Press ENTER. The formula removes the last character like in the picture below.
Step 6: Drag the Fill Handle and the rest of the Erroneous Names get the shape depicted in the image below.
Method 4: Using VALUE LEFT and LEN Function
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.
Step 1: Insert the following formula in any blank cell (C4)
Step 2: Hit ENTER, it removes the last unnecessary character.
Step 3: Drag the Fill Handle then you will come across a data shape similar to the picture below.
Method 5: Using Flash Fill
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.
Step 1: Click on the adjacent cell (C4) of an entry in the dataset.
Step 2: Type the 1st entry without the unnecessary character.
Step 3: Press ENTER and then Go to Data Tab >> Flash Fill (In Data Tools Section).
Step 4: Click on the Flash Fill option. All the number values will appear, omitting the characters.
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 above explained methods worthy of your quest. Comment, if you need further clarifications and have something to add.