Remove Last Character from String Excel (5 Easy Methods)

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.

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 and ID.

dataset - Remove Last Character from String Excel

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

REPLACE(old_text, start_num, num_chars, new_text)

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)

=REPLACE(B4,9,1,” “)

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 (” “).

replace function - Remove Last Character from String Excel

Step 2: Press ENTER. The data in the cell (C4) appear as we thought of.

replace function result

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.

replace function final result

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

LEN(text)

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

LEFT(text, [num_chars])

Step 1: Select any blank cell (C4). Paste the formula

=LEFT(B4, LEN(B4) – 1).

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.

left and len function - Remove Last Character from String Excel

Step 2: Press ENTER. It replaces the last character in Erroneous Name.

left and len function result

Step 3: Drag the Fill Handle and the rest of the Erroneous Names look similar to the image below.

left and len function final result

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

RemoveLC = Left(str, Len(str) – num_chars)

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.

vba macro code method

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

vba macro code Remove Last Character from String Excel

Step 4: Go to the worksheet and Type

=RemoveLC(B4,1)

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.

vba macro code function

Step 5: Press ENTER. The formula removes the last character like in the picture below.

vba macro code function result

Step 6: Drag the Fill Handle and the rest of the Erroneous Names get the shape depicted in the image below.

vba macro code function final result

Read more: How to Remove the First Character from a String in Excel with VBA

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)

=VALUE(LEFT(B4,LEN(B4)-1))

value left and len function Remove Last Character from String Excel

Step 2: Hit ENTER, it removes the last unnecessary character.

Result LEFT LEN function

 

 

Step 3: Drag the Fill Handle then you will come across a data shape similar to the picture below.

value left len function final result

 

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.

flash fill feature Remove Last Character from String Excel

Step 3: Press ENTER and then Go to Data Tab >> Flash Fill (In Data Tools Section).

flash fill feature direction

Step 4: Click on the Flash Fill option. All the number values will appear, omitting the characters.

flash fill feature final result

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 above explained methods worthy of your quest. Comment, if you need further clarifications and have something to add.


Further Readings:

Maruf Islam

I, Maruf Islam, an engineer, content writer. I completed my BSc from Bangladesh University Of Engineering & Technology, want to pursue a career in content writing & development.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo