Debugging spaces in VBA

Question

I have a cell in a table that says:

64123 , where there is some kind of empty space after 3. I thought it was empty space, so in my vba code I tried several ways to get this OUT space from the string, but not application.trim and replace .

 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 

In the end, of course, there is empty space - I see this when I select a cell in Excel, and application.trim ALWAYS works for me. What else could be other than empty space? If it returns a tab or a carriage, what is the replace syntax for them for?

Answer 1

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 

only for the 7th char

 Sub tellMeWhatCharValues(h As String) Dim i MsgBox Asc(Mid(h, 7, 1)) End Sub 

Answer 2

To add answers to a Sorceri question:

There are several options for space symbols that can complicate the situation, for example, Thin Space , Zero Width Space , Non-breaking space , and more .

Trim will not catch all of these (and probably will not remove the protected space, No. 160).
Some of them are Unicode characters that can return a question mark (ascii 63 code) using Asc .
AscW / ChrW can be used for Unicode characters (and work with ascii characters).

Try the following:

 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 

Answer 3

Complex spaces are often CHAR(160) , as you find, see Removing leading or trailing spaces in an entire data column

In your case, you can delete them with code using

 ActiveSheet.UsedRange.Replace Chr(160), vbNullString