I have a cell in a table that reads:
64123
where there is some sort of white space after 3. I thought it was a blank space, so in my vba code, I've tried multiple ways of getting this blank space OUT of the string, but neither application.trim
nor replace
work.
With Cells(c.Row, 16)
.NumberFormat = "General"
v = Application.Trim(.Text)
v = Replace(v, " ", "")
Debug.Print v
.Clear
.NumberFormat = "General"
.Value = Application.Trim(v)
End With
There is definitely a blank space at the end - I can see it when I highlight the cell in Excel, and Application.Trim
has ALWAYS worked for me. What else could this be other than a blank space? If it's a tab or a carriage return, what's the replace
syntax for those?
The VBA SPACE function is listed under the text category of VBA functions. When you use it in a VBA code, it returns a string which is consisting of a specified number of spaces. In simple words, with space function, you can create spaces by supplying numbers.
White space is ignored in VBA and the editor will automatically discard any extra spaces. The exception is when whitespace is included within double quotes, as part of a string. You can use the Tab key to indent your lines of code that logically belong together to make the code easier to read.
One of the methods used to debug VBA code is by running the code. The shortcut key for the command is F5. Start by placing the cursor into the UserForm or Sub (macro) and then press F5 to run the sub. Please note that F5 will not work when running a sub that requires parameters to execute a function.
run this and it will tell you all the ascii values of your string
Sub tellMeWhatCharValues(h As String)
Dim i
For i = 1 To Len(h)
MsgBox Asc(Mid(h, i, 1))
Next i
End Sub
for just the 7th char
Sub tellMeWhatCharValues(h As String)
Dim i
MsgBox Asc(Mid(h, 7, 1))
End Sub
To add some points to Sorceri's answer:
There are a couple of variations of space characters that can make things more complicated, like Thin Space, Zero Width Space, Non-breaking Space, and more.
Trim
will not catch all of these (and probably shouldn't remove a protected space, #160).
Some of these are unicode-characters, that may return a question mark (ascii code 63) with Asc
.AscW
/ChrW
can be used for unicode characters (and work with ascii characters as well).
Try this:
Public Sub test()
Dim s As String
s = "12345z" & Chr(160) & "x"
Analyze s
Debug.Print "---------"
s = "12345z" & ChrW(8239) & "x" ' #8239 = narrow no-break space
Analyze s
End Sub
Public Sub Analyze(s)
Dim c As String
Dim i As Integer
For i = 1 To Len(s)
c = Mid(s, i, 1)
Debug.Print c & " => " & AscW(c)
Next
End Sub
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With