how to find and identify unicode chars

[Harvey]

Hello all,

I have a number of xls files, which I have to 'clean up' following some guidelines. This are the ones I am having trouble with. Can anybody point me in the right direction?

- There are Unicode characters (UTF-8) in the document. Not all of these characters display. Remove all characters that do not display the corresponding symbol (remove all 'squares').

There is lots of Unicode, but when I use ASC I get their ASCII value. How can I 'see' when a character is Unicode? Furthermore, how can I 'see' if it's unrecognized?

- Replace ASCII character '16' with Unicode character '63'.

How to do this?

Thanks for the help already,

Kind regards,

Martien de Jong

 

[Harvey]

I need at least one reply to make sure that someone has actually read the topic.
Please help me someone

 

[Jonmo1]

I need at least one reply to make sure that someone has actually read the topic

You can see how many people have read it by looking at the "Views" Column on the Forum.

- Replace ASCII character '16' with Unicode character '63'.

=SUBSTITUTE(A1,CHAR(16),CHAR(63))

How can I 'see' when a character is Unicode?

In any blank column, Row 1 put..
=CHAR(ROW())
Fill down.

 

[Harvey]

Hi there,

Thanks for the reply, but...
none of your stuff is working.
I found the ChrW function for generating unicode.
Now, only this question stays open:

I need to find all unrecognized unicode characters in my sheet, and delete them.

 

[Richard Schollar]

Hi Harvey

How many of these characters are there likely to be in this document? Are they restricted to a single worksheet, or spread across several? How many cells on each spreadsheet? I ask because I can think of a way of doing it but it will iterate thru every character in every cell containing data to identify any of these characters and remove them. This will potentially take an exceptionally long time. Alternatively, you may be able to use a regular expression, but I am unsure as to whether a regex will remove these characters (you could send me a small sample of your data to test if you would like - I will give you my email if you would like me to try this).

 

[Harvey]

Hello RichardSchollar,

Thank you for your offer.
I have just solved the problem by assuming there are only three unrecognized characters (the only ones I could find, that weren't recognized).
As for clarification, I will answer your questions.
I use ChrW in a Range.Replace now and it does the job.
The 'documents to edit' are about 200 xls files, each containing one sheet, with 10.000 rows, 20-50 columns. They are literally filled with gibberish, of which most unicode chars.
The range.replace now finished in about 3 seconds per document, which is still acceptable.
A colleague of mine said he would do it in Java, which would offer a much better performance, but he is on vacation now :'(
Anyways, thank you for the input guys. If I find the Java solution I will post it for anyone interested.
Cheers and see you next time

P.S. How would you check if a character is 'recognized', I couldnt come up with anything for that.

 

[Richard Schollar]

I wouldn't check if it was recognized - I would test the character set of the character to see if it was 0 (standard ANSI) or anything else (ie something you don't want):

'assume A1 contains a string and you want to check it contains no funny characters
Dim s as String, b() as Byte, i As Long
s = Range("A1").Value
b=s
For i = 1 to UBound(b) Step 2
  If b(i)>0 Then
    'this character is not from the standard character set! Need to do something with it!
  End If
Next

Much slower than a straightforward Replace operation which you have gone for, however

 

[Harvey]

That is just what I was looking for,
I am definitely going to use that later on!
Thanks for posting the code.

 

[Harvey]

By the way, it actually runs much faster through the sheet than I expected.
Thanks again.