Removing hidden Chr within text, Chr63

USAMax

Well-known Member
Joined
May 31, 2006
Messages
832
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Rich (BB code):
If InStr(1, Cells(x, 1), Chr(63)) > 0 Then
 

USAMax

Well-known Member
Joined
May 31, 2006
Messages
832
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,187
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 1, 2010
Messages
644

ADVERTISEMENT

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

Well-known Member
Joined
May 31, 2006
Messages
832
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 1, 2010
Messages
644

ADVERTISEMENT

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.
Code:
=LEN(A2)

Now run this Sub
Code:
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 [COLOR=#ff0000][B]AscW[/B][/COLOR](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.
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,187
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Mar 1, 2010
Messages
644
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,187
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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).
 

Forum statistics

Threads
1,137,059
Messages
5,679,368
Members
419,823
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top