VBA: How to Remove Leading and Trailing Spaces

You can use the following basic syntax to remove leading and trailing spaces from strings using VBA:

Sub RemoveLeadingTrailingSpaces()

    Dim i As Integer

    For i = 2 To 7
    Range("B" & i) = Trim(Range("A" & i))
    Next i
    
End Sub

This particular example removes the leading and trailing spaces from each string in the range A2:A7 and outputs the results in the range B2:B7.

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

Example: Using VBA to Remove Leading and Trailing Spaces from Strings

Suppose we have the following list of strings in Excel:

Notice that several of the strings have either leading or trailing spaces.

Suppose we would like to remove both the leading and trailing spaces from each string.

We can create the following macro to do so:

Sub RemoveLeadingTrailingSpaces()

    Dim i As Integer

    For i = 2 To 7
    Range("B" & i) = Trim(Range("A" & i))
    Next i
    
End Sub

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

Column B displays each of the strings in column A with the leading and trailing spaces removed.

Note that if you’d like to only remove the leading or trailing spaces from strings, you can use the LTrim or RTrim functions, respectively.

Note: You can find the complete documentation for each of the VBA Trim functions here.

The following tutorials explain how to perform other common tasks using VBA: