VBA: How to Remove Last Character from String

You can use the following basic syntax to remove the last character from a string using VBA:

Sub RemoveLastChar()

    Dim i As Integer
    Dim myString As String

    For i = 2 To 11
    myString = Range("A" & i)
    Range("B" & i) = Left(myString, Len(myString) - 1)
    Next i
    
End Sub

This particular example removes the last character from each string in the range A2:A11 and outputs the results in the range B2:B11.

The following example shows how to use this syntax in practice.

Example: Using VBA to Remove Last Character from Strings

Suppose we have the following list of basketball team names in Excel:

Suppose we would like to remove the last character from each team name.

We can create the following macro to do so:

Sub RemoveLastChar()

    Dim i As Integer
    Dim myString As String

    For i = 2 To 11
    myString = Range("A" & i)
    Range("B" & i) = Left(myString, Len(myString) - 1)
    Next i
    
End Sub

When we run this macro, we receive the following output:

VBA remove last character from string

Column B displays each of the strings in column A with the last character removed.

If you would instead like to remove the last n characters from a string, simply change the 1 in the Left method to a different number.

For example, we can create the following macro to remove the last 2 characters from a string:

Sub RemoveLastTwoChar()

    Dim i As Integer
    Dim myString As String

    For i = 2 To 11
    myString = Range("A" & i)
    Range("B" & i) = Left(myString, Len(myString) - 2)
    Next i
    
End Sub