Table of Contents
Using VBA, Excel users can remove leading and trailing spaces from their data by writing a macro that replaces the spaces with an empty string. This can be done by looping through the range of cells, selecting each cell, using the Replace function to substitute the spaces with nothing, and then moving to the next cell. This will effectively remove all the leading and trailing spaces from the data in the range of cells.
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 .