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: 1. LTrim Removes leading spaces from a string.

   Dim originalString As String
   Dim trimmedString As String

   originalString = "    Hello, World!"
   trimmedString = LTrim(originalString) ' The result is "Hello, World!"
   
2. RTrim Removes trailing spaces from a string.

   Dim originalString As String
   Dim trimmedString As String

   originalString = "Hello, World!    "
   trimmedString = RTrim(originalString) ' The result is "Hello, World!"
   
3. Trim Removes both leading and trailing spaces from a string.

   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:

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.

Unlock Your Potential

Excel

Basic - Advanced

Access

Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *