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.
Additional Resources
The following tutorials explain how to perform other common tasks using VBA:
VBA: How to Count Occurrences of Character in String
VBA: How to Check if String Contains Another String
VBA: How to Count Cells with Specific Text