VBA Trim, LTrim and RTrim for Removing spaces

VBA Functions to remove white spaces

VBA has a few string functions that remove spaces in strings or cells in an Excel sheet.

An example of removing spaces by Trim function

Let us start with simple text passing to the Trim function with spaces on both sides.

The following string is passed to the Trim function:

See the code and output in the message box:

Sub trim_ex()

MsgBox "     Hello, this is VBA    " & vbNewLine & vbNewLine & Trim("     Hello, this is VBA    ")

End Sub

Output:

You can see the difference between text with and without Trim which is displayed in the message box.

Using a string variable example

Now declaring and assigning a sentence to a string variable with leading and trailing spaces and then pass the string variable to the Trim function.

The string is displayed before and after using the Trim function:

Sub trim_ex()

Dim str As String

str = "   VBA Tutorial  "

MsgBox str & vbNewLine & vbNewLine & Trim(str)

End Sub

Output:

The first string is original while the second line is after using the Trim function.

Using Trim function for an Excel sheet cell

We used a Range object and specified A2 cell that contains text and leading and trailing spaces (shown below).

By using Trim, we will remove those spaces of the A2 cell:

Sub trim_ex()

Range("A2").Value = Trim(Range("A2"))

End Sub

Result:

Removing spaces from a range of cells example

Now, we provide a range of cells and use a For..Each loop to remove spaces of multiple cells (A2 to A5).

Code:

Sub trim_ex()

Dim rng As Range
Set rng = Range("A1:A5")

For Each cell In rng

    cell.Value = Trim(cell)

Next

End Sub

Output:

Remove trailing spaces by LTrim

The example below shows removing trailing spaces only by LTrim function in VBA.

The message box makes it clear.

Code:

Sub trim_ex()

MsgBox "     LTrim Remove Leading Spaces Only    " & "." & vbNewLine & vbNewLine & LTrim("     LTrim Remove Leading Spaces Only    ") & "."

End Sub

Result:

Delete leading spaces by

Similarly, RTrim only removes the trailing spaces from the given string.

See an example below:

Sub trim_ex()

MsgBox "     RTrim Remove Trailing Spaces Only    " & "." & vbNewLine & vbNewLine & RTrim("     RTrim Remove Trailing Spaces Only    ") & "."

End Sub

Output:

You can see that only spaces from the right are removed while the left side is at the same level.