VBA: How to Remove Spaces from String

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

Sub RemoveSpaces()

    Dim i As Integer

    For i = 2 To 8
    Range("B" & i) = Replace(Range("A" & i), " ", "")
    Next i
    
End Sub

This particular example removes the spaces from each string in the range A2:A8 and outputs the results in the range B2:B8.

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

Example: Using VBA to Remove Spaces from Strings

Suppose we have the following list of strings in Excel:

Suppose we would like to remove the spaces from each string.

We can create the following macro to do so:

Sub RemoveSpaces()

    Dim i As Integer

    For i = 2 To 8
    Range("B" & i) = Replace(Range("A" & i), " ", "")
    Next i
    
End Sub

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

VBA remove spaces from string

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

Note: You can find the complete documentation for the VBA Replace method here.

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