VBA Function: Trim

Description

The VBA TRIM Function returns a string after removing spaces from the left and right of the string.

TRIM Syntax

Trim(text)

VBA Trim Example

Remove extraneous spaces from the beginning and end of the string:

Sub TrimExample1()

     MsgBox Trim("test")              'Returns: "test"
     MsgBox Trim("   test")           'Returns: "test"
     MsgBox Trim("test   ")           'Returns: "test"
     MsgBox Trim("   test   ")        'Returns: "test"
     MsgBox Trim("   test   test   ") 'Returns: "test   test"
     MsgBox Trim("   ")               'Returns: ""
    
End Sub

The Trim function in VBA deletes leading and trailing spaces only. It does not touch double spaces inside a string. You should use a TRIM worksheet function for that.

VBA Trim Example to eliminate extra spaces inside a text

Remove extraneous spaces from the inside of the string:

Sub TrimExample2()

     MsgBox WorksheetFunction.TRIM("   test     test   ") 'Returns: "test test"
    
End Sub

To remove spaces from the left side of the string only, use the LTRIM function.

To remove spaces from the right side of the string only, use the RTRIM function.