VBA Trim, LTrim and RTrim for Removing spaces

VBA has a few string functions that is used to remove spaces in strings, or cells of an Excel sheet.

VBA Functions to remove white spaces

VBA has a few string functions that are used to remove spaces in strings, or cells of an Excel sheet.

These are:

  • Trim – Removes leading and trailing spaces in  a string
  • LTrim – Remove spaces from the left of the string
  • RTrim – Remove spaces from the right of the string

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:

“     Hello, this is VBA    “

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:

VBA-Trim

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 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:

VBA-Trim-variable

The first string is original while the second line string is with 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)

VBA-Trim-cell-sample

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

Sub trim_ex()

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

End Sub

Result:

VBA-Trim-cell

Removing spaces from a range of cells example

Now providing a range of cells and using 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:

VBA-Trim-cell-multiple

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:

VBA-LTrim

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:

VBA-RTrim

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