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:
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:
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)
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:
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:
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, only spaces from the right are removed while the left side is at the same level.