Removing hidden Chr within text, Chr63

[USAMax]

I am using Excel 2010 and cleaning up some data and I have two cells that appear the same but when I compare ActiveCell to ActiveCell.Offset(1,0) they do not match.

When I broke it down I found the mid string that did not match and I got its value using

SndChr = Asc(Mid(ActiveCell.Offset(1, 0), StrVal, 1))
And this returned a value of 63.

I thought I was home free and I could search the string for Chr(63) but it does not find it.

If InStr(1, Cells(x, 1), Asc(63)) > 0 Then

Where am I going wrong?

 

[VoG]

Try

If InStr(1, Cells(x, 1), Chr(63)) > 0 Then

 

[USAMax]

I am sorry but I tried your text first and should have entered that into my original question. Just for the heck of it I tried Asc(63) and that is what I cut and pasted by mistake.

SndChr = Asc(Mid(ActiveCell.Offset(1, 0), StrVal, 1))
SndChr = 63

InStr(1, Cells(x, 1), Chr(63)) = 0
or
InStr(1, Cells(x, 1), Chr(Mid(ActiveCell.Offset(1, 0), StrVal, 1))) = 0

Either way it does not find Chr(63) in the string.

 

[Rick Rothstein]

I am using Excel 2010 and cleaning up some data and I have two cells that appear the same but when I compare ActiveCell to ActiveCell.Offset(1,0) they do not match.

When I broke it down I found the mid string that did not match and I got its value using

SndChr = Asc(Mid(ActiveCell.Offset(1, 0), StrVal, 1))
And this returned a value of 63.

I thought I was home free and I could search the string for Chr(63) but it does not find it.

If InStr(1, Cells(x, 1), Asc(63)) > 0 Then

Where am I going wrong?

I think you have a Unicode character whose ASCII/ANSI value is greater than 255. Asc cannot see Unicode characters and returns 63 (the question mark) for them. Try using AscW instead of Asc in your code and I think you will get a more meaningful number back from it.

 

[Marcol]

Char(63) is ?, if there is a question mark in your string then you will find it.

However Excel also returns ? when it comes across a non-ascii character or a character that you don't have installed in your type styles. e.g. some Hindi characters.

This can be very confusing and it is often difficult to trace the relevant unicode character code.

 

[USAMax]

Is there a way to test for this string without going through each character?

I would have to assume that if acs(Mid... returns 63 then that would always be the same as chr(63) but you are saying that I am wrong?

 

[Marcol]

There are a great number of possibilities, in theory at least, that a space, a very small space, or an invisible character could be a unicode character above 255, that looping through might be the only option.

In practice there will be much less, but how many, I don't know.

Try copying this to A2 in a spare sheet abcde Ἃf
In B2, drag down to B3.

=LEN(A2)

Now run this Sub

Sub CleanUnicode()
    Dim n As Long, strClean As String, strChr As String
    strClean = Range("A2")
    For n = Len(strClean) To 1 Step -1
        strChr = Mid(strClean, n, 1)
        If AscW(strChr) > 255 Then
            strClean = Replace(strClean, strChr, " ")
        End If
    Next
    Range("A3") = WorksheetFunction.Trim(strClean)
End Sub

I have used " " in the Replace function to show invisible characters. you might want to use "" instead.

This should clean your string but it might also strip out characters you might need, such as Ἃ in the example.
The space is unicode character 8192.

WorksheetFunction.Trim(), trims internal blanks to one space, as well as leading and trailing spaces.

VBa Trim() only trims leading and trailing spaces.

 

[Rick Rothstein]

This should clean your string but it might also strip out characters you might need, such as Ἃ in the example.
The space is unicode character 8192.

WorksheetFunction.Trim(), trims internal blanks to one space, as well as leading and trailing spaces.

VBa Trim only trims leading and trailing spaces.

Here is a link to a posting I made which includes a more focused "clean" function... perhaps the OP can make some use of it.

Trim all Cells in a Worksheet - VBA

 

[Marcol]

Does that Function clean the example string I posted?
i.e. abcde Ἃf
If copied from the forum the string should have 2 invisible chrs, Unicode 8298, one space 8192, and the character 7947

I agree that I posted a bit of a sledge-hammer solution, but it was only intended to illustrate a point.

 

[Rick Rothstein]

Does that Function clean the example string I posted?
i.e. abcde Ἃf

No, it doesn't... and in thinking about it more closely, my function probably is on no use to the OP for his current situation. Your function is probably close to doing what the OP needs. Yes, some valid Unicode characters might get hosed, but the problem with permitting some Unicode characters into a text string is... which other Unicode characters are not permissible? I mean, what distinguishes Ἃ as being permissible and some other Unicode character as not. To determine that would require some kind of delineated rule... I am not sure the OP would be able to delineate such a rule other than to say any character whose ASCII/ANSI code is greater than 255 is not allowed (which is exactly what your function does).