VBA - String Manipulation empty spaces

Question

I am using WinHttp.WinHttpRequest to retrieve information from a site. However the retrieved information comes with some empty spaces before the beginning, which can be removed with LeftTrim function.

But even after the string is trimmed I still get some annoying things before the first letter.

Text before Ltrim
"     Hello World!"

Text after Ltrim
"  Hello World!"

Note: Using left to display the first char of the trimmed text returns "", but trying to use IF comparison to "" doesn't work so I can't get rid of these "".

If left("  Hello World!",1) = "" then 
   ' code to remove ""

How do I remove this damn things, what is this?

Try http://stackoverflow.com/a/9582919/2294676 - formula-based but could be useful — Lydia Ralph, Jul 01 '15 at 11:26

Alex K. · Accepted Answer · 2015-07-01T11:13:50.580

Firstly identify the rogue character - probably a tab:

str = trim$(str)

?"""" & str & """"
"   Hello World!"

'//what is the char code of the first char?
?ascw(left$(str, 1))
 9 '// tab

Then implement a custom LTrim, E.g.

Function LTrimWs(str As String) As String
    Dim i As Long
    For i = 1 To Len(str)
        Select Case Mid$(str, i, 1)
            Case " ", ChrW$(9)
               Mid$(str, i, 1) = " "
            Case Else
               LTrimWs = LTrim$(str)
               Exit For
        End Select
    Next
End Function

score 0 · Answer 2 · answered Jul 01 '15 at 11:07

0

You want to use

Trim("    Hello World")

This will trim any blank spaces off the front and rear of the string.

answered Jul 01 '15 at 11:07

99moorem

1,955
1
15
27

VBA - String Manipulation empty spaces

2 Answers2