Trim Function does not remove the spaces - VBA Excel

Question

I've seen a few topics related to this question, unfortunately non of them helped. Trim simply doesn't remove the spaces neither before or after...

Macro should be going through column "F" ad trim all the edges, currently id does go through the column and with the help of MsgBox I saw that it get's all the values in the cells correctly, yet the actual trimming doesn't work.

Sub trimAllTrends()

Dim i As Integer
Dim allInLastRow As Long
Dim allInWs As Worksheet
Dim myString As String

Set allInWs = ThisWorkbook.Worksheets("All Trends")
allInLastRow = allInWs.Range("C" & Rows.Count).End(xlUp).Row

For i = 2 To allInLastRow
    
    myString = allInWs.Cells(i, 6).Value
                'MsgBox (myString)
                'WorksheetFunction.Trim (allInWs.Cells(i, 6))
                'allInWs.Cells(i, 6).Value = LTrim(allInWs.Cells(i, 6).Value)
                '.Cells(i, "F").Value = Application.Trim(.Cells(i, "F").Value)
    WorksheetFunction.Trim (myString)
    
Next i


End Sub

Any help is much appreciated!

Thank you in advance!

Answer 1

I have to say I've found the Trim() function a bit limited, especially with data imported from a word processing or page design application. So the points everyone makes in the comments are all good ones.

If it's of interest to you, I use my own TrimWhitespace() function. There are doubtless quicker ways of doing it, but I find this one suits my purposes:

Public Function TrimWhitespace(txt As String) As String
    Dim i As Long, j As Long, c As Long
    Dim startPos As Long, endPos As Long
    Dim whitespaces As Variant
    Dim isWhitespace As Boolean
    
    ' List of whitespace characters.
    whitespaces = Array( _
        &H9, &HA, &HB, &HC, &HD, &H20, &H85, &HA0, _
        &H1680, &H2000, &H2001, &H2002, &H2003, &H2004, &H2005, &H2006, _
        &H2007, &H2008, &H2009, &H200A, &H2028, &H2029, &H202F, &H205F, _
        &H3000, &H180E, &H200B, &H200C, &H200D, &H2060, &HFEFF)
    
    ' Find the first non-whitespace.
    For i = 1 To Len(txt)
        c = Asc(Mid(txt, i, 1))
        isWhitespace = False
        For j = LBound(whitespaces) To UBound(whitespaces)
            If c = whitespaces(j) Then
               isWhitespace = True
               Exit For
            End If
        Next
        If Not isWhitespace Then
            startPos = i
            Exit For
        End If
    Next
    
    ' If there's no start position, return an empty string.
    If startPos = 0 Then Exit Function
    
    ' Find the last non-whitespace.
    For i = Len(txt) To startPos Step -1
        c = Asc(Mid(txt, i, 1))
        isWhitespace = False
        For j = LBound(whitespaces) To UBound(whitespaces)
            If c = whitespaces(j) Then
               isWhitespace = True
               Exit For
            End If
        Next
        If Not isWhitespace Then
            endPos = i
            Exit For
        End If
    Next
    
    TrimWhitespace = Mid(txt, startPos, endPos - startPos + 1)
End Function

Here's some testing code to demonstrate it:

Public Sub RunMe()
    Dim txt1 As String, txt2 As String
    
    txt1 = Chr(32) & Chr(160) & Chr(9) & "abc" & Chr(32) & Chr(160) & Chr(9)
    txt2 = Chr(32) & Chr(160) & Chr(9) & "xyz" & Chr(32) & Chr(160) & Chr(9)
    txt1 = Trim(txt1)
    txt2 = TrimWhitespace(txt2)
    
    Debug.Print "TRIM RESULTS"
    Debug.Print "============"
    Debug.Print "Trim()"
    Debug.Print "------"
    Debug.Print "Trimmed: |" & txt1 & "|"
    Debug.Print "Desired: |abc|"
    Debug.Print
    Debug.Print "TrimWhitespace()"
    Debug.Print "------------------------"
    Debug.Print "Trimmed: |" & txt2 & "|"
    Debug.Print "Desired: |xyz|"
End Sub