VBA Trim leaving leading white space

Question

I'm trying to compare strings in a macro and the data isn't always entered consistently. The difference comes down to the amount of leading white space (ie " test" vs. "test" vs. " test")

For my macro the three strings in the example should be equivalent. However I can't use Replace, as any spaces in the middle of the string (ex. "test one two three") should be retained. I had thought that was what Trim was supposed to do (as well as removing all trailing spaces). But when I use Trim on the strings, I don't see a difference, and I'm definitely left with white space at the front of the string.

So A) What does Trim really do in VBA? B) Is there a built in function for what I'm trying to do, or will I just need to write a function?

Thanks!

Answer 1

So as Gary's Student aluded to, the character wasn't 32. It was in fact 160. Now me being the simple man I am, white space is white space. So in line with that view I created the following function that will remove ALL Unicode characters that don't actual display to the human eye (i.e. non-special character, non-alphanumeric). That function is below:

Function TrueTrim(v As String) As String
Dim out As String
Dim bad As String
bad = "||127||129||141||143||144||160||173||" 'Characters that don't output something
       'the human eye can see based on http://www.gtwiki.org/mwiki/?title=VB_Chr_Values

out = v

'Chop off the first character so long as it's white space
If v <> "" Then
    Do While AscW(Left(out, 1)) < 33 Or InStr(1, bad, "||" & AscW(Left(out, 1)) & "||") <> 0 'Left(out, 1) = " " Or Left(out, 1) = Chr(9) Or Left(out, 1) = Chr(160)
        out = Right(out, Len(out) - 1)
    Loop

    'Chop off the last character so long as it's white space
    Do While AscW(Right(out, 1)) < 33 Or InStr(1, bad, "||" & AscW(Right(out, 1)) & "||") <> 0 'Right(out, 1) = " " Or Right(out, 1) = Chr(9) Or Right(out, 1) = Chr(160)
        out = Left(out, Len(out) - 1)
    Loop
End If 'else out = "" and there's no processing to be done

'Capture result for return
TrueTrim = out
End Function

Answer 2

TRIM() will remove all leading spaces

Sub demo()
    Dim s As String
    s = "   test   "
    s2 = Trim(s)
    msg = ""
    For i = 1 To Len(s2)
        msg = msg & i & vbTab & Mid(s2, i, 1) & vbCrLf
    Next i
    MsgBox msg
End Sub

It is possible your data has characters that are not visible, but are not spaces either.