Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Removing hidden Chr within text, Chr63

  1. #1
    Board Regular
    Join Date
    May 2006
    Location
    West Chicago, Illinois
    Posts
    795
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Removing hidden Chr within text, Chr63

    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?

  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Removing hidden Chr within text, Chr63

    Try

    Code:
    If InStr(1, Cells(x, 1), Chr(63)) > 0 Then
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    May 2006
    Location
    West Chicago, Illinois
    Posts
    795
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Removing hidden Chr within text, Chr63

    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.

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,514
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Removing hidden Chr within text, Chr63

    Quote Originally Posted by USAMax View Post
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Board Regular Marcol's Avatar
    Join Date
    Mar 2010
    Location
    Fife, Scotland
    Posts
    644
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Removing hidden Chr within text, Chr63

    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.

  6. #6
    Board Regular
    Join Date
    May 2006
    Location
    West Chicago, Illinois
    Posts
    795
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Removing hidden Chr within text, Chr63

    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?

  7. #7
    Board Regular Marcol's Avatar
    Join Date
    Mar 2010
    Location
    Fife, Scotland
    Posts
    644
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Removing hidden Chr within text, Chr63

    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 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.
    Last edited by Marcol; Nov 17th, 2012 at 02:54 PM.

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,514
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Removing hidden Chr within text, Chr63

    Quote Originally Posted by Marcol View Post
    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
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    Board Regular Marcol's Avatar
    Join Date
    Mar 2010
    Location
    Fife, Scotland
    Posts
    644
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Removing hidden Chr within text, Chr63

    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.

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,514
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Removing hidden Chr within text, Chr63

    Quote Originally Posted by Marcol View Post
    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).
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •