Excel VBA to Remove Last Character from String (3 Examples)

Removing the last character or a specific number of last characters from a string is a common task for cleaning data. While Excel’s built-in formulas can handle this, using VBA provides a more permanent solution. By applying VBA code, you can remove the last character from multiple cells at once. In this article, we will guide you through three different VBA methods to remove the last character from a string.

Sub RemoveLastChar_Range()
Dim cell As Range
For Each cell In Selection
If Len(cell.Value) > 0 Then
cell.Value = Left(cell.Value, Len(cell.Value) - 1)
End If
Next cell
End Sub

Here, we will remove the same number of characters from the end of a string in a selected range of cells. This VBA macro will run through each cell and trim the specified number of characters from the right.

Let’s imagine we have a list of Order IDs in column A that all end with the character “A”. Here, we will remove this last character from each Order ID.

➤ Select cells (A2:A11) and go to the Developer tab and click on Visual Basic.

➤ In the VBA editor, go to Insert > Module.

➤ Now, copy and paste the following code into the new module window and hit Run.

Sub RemoveLastChar_Range()
Dim cell As Range
For Each cell In Selection
If Len(cell.Value) > 0 Then
cell.Value = Left(cell.Value, Len(cell.Value) - 1)
End If
Next cell
End Sub

After running the macro, the last character “A” will be removed from all the selected cells in column A.

This method is more flexible, as it allows you to dynamically choose how many characters to remove. A small input box will pop up, prompting you to enter the number of characters you want to delete from the end of the strings.

Let’s use the same sample data. Here, we will remove the last character “A” from the Order ID column, but this time, we will be prompted to specify the number of characters to remove.

➤ Select cells (A2:A11) and click Developer > Visual Basic.

➤ Insert a new module, put the code below and hit Run.

Sub RemoveLastChars()
Dim n As Long, r As Range, c As Range
On Error GoTo InvalidInput
n = CLng(InputBox("Enter number of characters to remove:"))
For Each r In Selection
If Len(r.Value) > n Then
r.Value = Left(r.Value, Len(r.Value) - n)
End If
Next r
Exit Sub
InvalidInput:
MsgBox "Enter a valid number less than or equal to the string length."
End Sub

After running the macro, an input box will appear.

➤ Enter 1 (or any number of characters you want to remove) and click OK.

The code will then remove the specified last character from each of the selected cells.

You can also create a user-defined function that works on a range of cells, immediately removing the last characters. This function is particularly useful when dealing with large datasets, as it can process an entire range much faster than a cell-by-cell function.

➤ In the VBA editor, click Insert > Module, and paste the following code and hit Save.

Function TrimLastChars(rng As Range, n As Long) As Variant
Dim data As Variant, i As Long, j As Long
data = rng.Value
For i = 1 To UBound(data, 1)
For j = 1 To UBound(data, 2)
If Len(data(i, j)) > n Then
data(i, j) = Left(data(i, j), Len(data(i, j)) - n)
End If
Next j
Next i
TrimLastChars = data
End Function

➤ After saving the code, return to the worksheet to use the user-defined function.
➤ Choose a cell and write the formula below.

As a result, the last three characters will be removed from all the cells.

Frequently Asked Questions

Will these macros work on numbers as well?

These codes will work if the numbers are stored as text. For numeric values, you can convert them to text using the CStr() function in VBA before processing.

How can I remove the last character only if it’s a specific symbol, like a dash or comma?

You can add a line inside the code If Right(cell.Value, 1) = “-” Then to check before removing the character.

What if my data has formulas? Will the macro keep them?

No, the macro replaces the formula with its resulting text. If you want to keep formulas, consider applying the logic within a formula instead of a macro.

Concluding Words

Above, we have explored different ways to remove the last character from a string using VBA. Whether you need to delete a fixed number of characters, let the user specify how many to remove, or use a custom function within your worksheet, these methods make the task easy for you. If you have any further questions, feel free to share them below.