3

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!

JMichael
  • 157
  • 3
  • 14
  • 2
    `Trim` does remove both leading and trailing spaces. Are you sure that's a space? Try to `Print Asc(Txt)` to see if the ascii code is really 32 – stenci Jan 06 '15 at 21:54
  • Either your data does not contain a space (character code 32) at the start of the string, or your are implementing your code incorrectly. The VBA Trim function is well described in VBA Help for that function. It is somewhat different from the Worksheet function with the same name. – Ron Rosenfeld Jan 06 '15 at 21:55

6 Answers6

3

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
JMichael
  • 157
  • 3
  • 14
  • If you are going to trim the data, I would go with a Regular Expression solution, it is more general and robust – Jeanno Jan 06 '15 at 22:56
  • @Jeanno the regex solution below didn't catch all the characters, this solution did. – Ryu S. Dec 02 '18 at 19:44
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.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Turns out this is the case. I used Code(left({cell}m 1)) to get the character (160 for those who were curious). Given that I went ahead and created a function to clear the leading white space (any code value less than 32, 160, and a handful of others). – JMichael Jan 06 '15 at 22:42
2

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()

Chrismas007
  • 6,085
  • 4
  • 24
  • 47
0

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.

Jeanno
  • 2,769
  • 4
  • 23
  • 31
  • Comp2Strings = (InStr(str1, str2) <> 0 Or InStr(str2, str1) <> 0) – Maciej Los Jan 06 '15 at 22:07
  • This would fail with `str1 = " test"` and `str1 = "test "` – stenci Jan 06 '15 at 22:09
  • Based on the OP, only one of the strings will have leading or trailing space. So this would work – Jeanno Jan 06 '15 at 22:15
  • This would tell me if there were leading spaces (or white space characters depending on str2)m but I'm needing to clean off the shite space for later storage (sorry didn't reference that in original post, wasn't thinking about it as my problem was a InStr returning 0 when, to my brain, it should return a value >0) – JMichael Jan 06 '15 at 22:55
0

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
KevenDenen
  • 1,718
  • 13
  • 25
  • To my knowledge this should work. I've just never been a fan of regex, too easy to screw it up with how dense the code is. – JMichael Jan 06 '15 at 22:56
  • This will not work with the OP's problem. `\s` does NOT match the NBSP (character code 160) in vbscript. You could use a regex like `[\s\xA0]` though, with the appropriate beginning and end of line anchors, and multiline set to true. – Ron Rosenfeld Jan 07 '15 at 02:29
0

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.

certifiedtranslator.hu
  • 1
  • 2