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?

 

[Marcol]

Again agreed.

A sample of the data to be analysed would be an advantage to us all.

I'm fairly sure that this is a specific problem arrising from web derived data.

 

[Marcol]

This UDF might be a possibility.

I am not a VBa expert, so doubtless it can be improved.

Function CleanUnicode(rng As Range, _
                      Optional IncludeAscii As Boolean = True, _
                      Optional ShowAsSpace As Boolean = True, _
                      Optional TrimStr As Boolean = False) As String
    Dim n As Long
    Dim strClean As String, strChr As String
    
    strClean = rng
    
    For n = Len(strClean) To 1 Step -1
        strChr = Mid(strClean, n, 1)
        Select Case AscW(strChr)
            Case 8192 To 8207, 8234 To 8239, 8298 To 8303
               If ShowAsSpace Then
                    strClean = Replace(strClean, strChr, " ")
               Else
                    strClean = Replace(strClean, strChr, "")
                    n = Len(strClean)
               End If
        End Select
            
        If IncludeAscii Then
            Select Case Asc(strChr)
                Case 1 To 31, 127, 129, 141.143, 144, 157, 160
                   If ShowAsSpace Then
                        strClean = Replace(strClean, strChr, " ")
                   Else
                        strClean = Replace(strClean, strChr, "")
                        n = Len(strClean)
                   End If
            End Select
        End If
    Next
    If TrimStr Then
        CleanUnicode = WorksheetFunction.Trim(strClean)
    Else
        CleanUnicode = strClean
    End If
End Function

The default substitutes all non-printing characters,unicode spaces and invisible characters with a space, CHAR(32) e.g.

=CleanUnicode(A2)

The options as booleans are
1/. IncludeAscii > Default = True
2/. ShowAsSpace > Default = True
3/. TrimStr > Default = False

So this removes all non-printing characters and spaces

=CleanUnicode(A4,,FALSE)

And this trims the result if spaces are chosen

=CleanUnicode(A4,,FALSE)

i.e. The same as trimming the default function

=Trim(CleanUnicode(A2))

Try it out with this setup.
You will have to copy this abc??de??f for the string in A2, ExcelJeannie is returning ?s like your problem ...
Drag B2:D2 Down

Excel Workbook
	A	B	C	D
1	String	Length	UDF	New Length
2	abc??de ?f	10	abc de ?f	10
3	abc??de ?f	15	abc de ?f	15
4	Abcefg	7	Abc efg	7
5	Abcefg	7	Abc efg	7
Sheet1