How to use LTrim, RTrim, and Trim function in VBA?

In VBA (Visual Basic for Applications), the LTrim, RTrim, and Trim functions are used to remove leading spaces, trailing spaces, and both leading and trailing spaces from a string, respectively. Here’s how you can use each of these functions:

LTrim Removes leading spaces from a string

VBA


   Dim originalString As String
   Dim trimmedString As String

   originalString = "    Hello, World!"
   trimmedString = LTrim(originalString) ' The result is "Hello, World!"

RTrim Removes trailing spaces from a string

VBA


   Dim originalString As String
   Dim trimmedString As String

   originalString = "Hello, World!    "
   trimmedString = RTrim(originalString) ' The result is "Hello, World!"

Trim Removes both leading and trailing spaces from a string

VBA


   Dim originalString As String
   Dim trimmedString As String

   originalString = "    Hello, World!    "
   trimmedString = Trim(originalString) ' The result is "Hello, World!"

Here is an example of using these functions in a VBA procedure

VBA


Sub TrimExample()
    Dim str As String
    
    ' Original string with leading and trailing spaces
    str = "    Sample string    "
    
    ' Using LTrim
    Debug.Print "LTrim Example: *" & LTrim(str) & "*"
    
    ' Using RTrim
    Debug.Print "RTrim Example: *" & RTrim(str) & "*"
    
    ' Using Trim
    Debug.Print "Trim Example: *" & Trim(str) & "*"
End Sub

When you run this TrimExample subroutine, it will print the results in the Immediate Window of the VBA editor. Notice that I added asterisks (*) around the results to visualize where the spaces have been removed. Keep in mind that these functions remove only spaces and not other whitespace characters such as tabs or line breaks. If you need to remove other types of whitespace, you may need to use custom functions or replace methods to handle those scenarios.

facebooklinkedin