• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Non Printable Characters

GN0001

Member
Hello team,


I have a cell that I am sure it has non-printable or invisible characters. some times the end user enters some spaces inadvertently, that causes filter, some of functions and sort don't work. How can I find out what the number of those characters is in ANSI? Is it 160 or 27 or 143? So I can go ahead and remove them with substitute and clean and trim function?


Your help is greatly appreciate.

GGGGG
 
GGGGG


You can see the character codes for the first 255 characters using the Code() function

eg:

=Code(A1) will return the character code of the first character in A1

=Code(mid(A1,x,1)) will return the character code of the character in position x in A1

=Code(MID($A$1,ROW(),1)) in B1 then copy down will return all the characters codes from A1 in order down column B


If you have characters which are using the ANSI or higher ascii codes you will need a VBA function like:

[pre]
Code:
Function Characters(myString As Variant) As Variant
Dim i As Integer
For i = 1 To Len(myString)
Characters = Characters & AscW(Mid(myString, i, 1)) & ";"
Next i
End Function
[/pre]

To use =Characters(A1)

It will return something like: "72;101;108;108;111;160;71;71;71;71;71;"
 
Hui,

Thank you very much for the function and the code. That is a very big help. I will try the code and get back to you.

Respectively,

GGGGG
 
Hello Hui,

This Function:

CODE(MID(A1,ROW(),1))

doesn't work, because when I go to row 20, then start position become 20, the length of the text is only 5. So would you think the idea of Row()is a right argument for Mid function?

Regards,

GGGGG
 
Back
Top