CODEWITHSUNDEEP
stringexcelvba
JMichael
JMichael

Reputation: 177

VBA Trim leaving leading white space

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!

Upvotes: 4

Views: 7045

Answers (6)

certifiedtranslator.hu
certifiedtranslator.hu

Reputation: 1

Non-printables divide different lines of a Web page. I replaced them with X, Y and Z respectively.

Debug.Print Trim(Mid("X test ", 2)) ' first place counts as 2 in VBA

Debug.Print Trim(Mid("XY test ", 3)) ' second place counts as 3 in VBA

Debug.Print Trim(Mid("X Y Z test ", 2)) ' more rounds needed :)

Programmers prefer large text as may neatly be chopped with built in tools (inSTR, Mid, Left, and others). Use of text from several children (i.e taking .textContent versus .innerText) may result several non-printables to cope with, yet DOM and REGEX are not for beginners. Addressing sub-elements for inner text precisely (child elements one-by-one !) may help evading non-printable characters.

Upvotes: 0

KevenDenen
KevenDenen

Reputation: 1726

VBA's Trim function is limited to dealing with spaces. It will remove spaces at the start and end of your string.

In order to deal with things like newlines and tabs, I've always imported the Microsoft VBScript RegEx library and used it to replace whitespace characters.

In your VBA window, go to Tools, References, the find Microsoft VBScript Regular Expressions 5.5. Check it and hit OK.

Then you can create a fairly simple function to trim all white space, not just spaces.

Private Function TrimEx(stringToClean As String)
    Dim re As New RegExp
    ' Matches any whitespace at start of string
    re.Pattern = "^\s*"
    stringToClean = re.Replace(stringToClean, "")
    ' Matches any whitespace at end of string
    re.Pattern = "\s*$"
    stringToClean = re.Replace(stringToClean, "")
    TrimEx = stringToClean
End Function

Upvotes: 0

JMichael
JMichael

Reputation: 177

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

Upvotes: 4

Chrismas007
Chrismas007

Reputation: 6105

Without seeing your code it is hard to know, but you could also use the Application.WorksheetFunction.Clean() method in conjunction with the Trim() method which removes non-printable characters.

MSDN Reference page for WorksheetFunction.Clean()

Upvotes: 2

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

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.

Upvotes: 2

Jeanno
Jeanno

Reputation: 2859

Why don't you try using the Instr function instead? Something like this

Function Comp2Strings(str1 As String, str2 As String) As Boolean
    If InStr(str1, str2) <> 0 Or InStr(str2, str1) <> 0 Then
        Comp2Strings = True
    Else
        Comp2Strings = False
    End If
End Function

Basically you are checking if string1 contains string2 or string2 contains string1. This will always work, and you dont have to trim the data.

Upvotes: 0

Related Questions