Here Is An Example Of The VBA Trim Function

The “Trim” function is useful in eliminating the blank spaces in a string
normally caused by some data entry error.

Why do you want to do this? Well because a sorting error can happen if you are trying to
sort ” Dan, “Fred”, and “Ann”. ” Dan” will show up on top before “Ann” because of that space.

I normally use the “Trim” function before the “Len” function (which checks the length of the string in VBA), otherwise I can get an incorrect string length.

In this example we’ll read the values of a column and we’ll clean up the string that
has some stray spaces.

Sub VBATrimFunction()

    Dim rst As Object
    Dim strFname As String
    Dim intLen As Integer
    
    Set rst = CurrentDb.OpenRecordset("SELECT FirstName FROM tblNames ORDER BY ID")
    
    Do Until rst.EOF
        strFname = rst.Fields("FirstName")
        intLen = Len(strFname)
                
        Debug.Print "Name Before Trim: " & strFname
        Debug.Print "Length Before Trim: " & intLen
        
        strFname = Trim(strFname)
        intLen = Len(strFname)
        
        Debug.Print "Name After Trim: " & strFname
        Debug.Print "Length After Trim: " & intLen

        rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing

End Sub

Let me know if you have any questions, or click here for related posts

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!