Search for Non-ASCII Characters

[nanda83]

Hi all,

Can any one tell me how to search for Non-ASCII characters in EXCEL?


Thanks in advance!

Nanda

 

[Sal Paradise]

This formula gotten from here will highlight any character over ASCII 48:

Dim CharCount As Long 
Dim i As Integer 
CharCount = ActiveDocument.Characters.Count '  Count number of characters
Selection.HomeKey Unit:=wdStory ' Go to start of document
 
For i = 1 To CharCount '  Step through each character
    Selection.MoveRight Unit:=wdCharacter, Count:=1, Extend:=wdExtend ' Select character
    If Asc(Selection) < 48 Then ' Check if asc value is less than 48
        Selection. Range.HighlightColorIndex = wdYellow ' If less than 48 highlight in yellow
    End If 
    Selection.MoveRight Unit:=wdCharacter, Count:=1 ' Move to next character
Next

(Ascii 48 is '0' so this searches for non-ascii text but also highlights numbers)

 

[nanda83]

Thanks,

Me being a newbie.. Can you tell me where to place this code?

Or just refer to some site which explains it.

 

[Sal Paradise]

You would need to press Alt-F11 to open VBA, add 'Sub' tags to it, and then run it from the Macro menu in Excel (or press the 'play' button in VBA).

 

[Norie]

nanda83

What do you mean by 'non-ASCII characters'?

Have you looked at using the CLEAN worksheet function?

Or Find and Replace?

Sal

That code isn't going to work in Excel VBA, it doesn't have a ActiveDocument object.

As far as I know that's part of the Word object model.

 

[Sal Paradise]

By non-ASCII characters, I'm guessing the OP means double-byte characters (like Japanese, Chinese or Korean) or non-ascii code sets (Hebrew, Arabic, etc.). Often in my work we get a document which has to be translated from Japanese to English, and we need to remove all Japanese spaces (double-byte spaces), or for instance the °C mark double-byte character.

To wit (if you don't have Japanese support, you'll probably get garbage characters):
°C =/= ℃

Other potential muckups involve characters like ● circles in Japanese fonts instead of bullets or the like.

In regards to the code, my apologies. Couldn't the same sort of thing be done? Search for all characters that don't return ASCII values or somesuch? Since a Japanese character doesn't have an ascii code, it should work I'd think.

 

[Norie]

Sal

I'm sure it can be done, but like I said the code you posted is for Word not Excel.

There is no ActiveDocument object in Excel, which you can check by opening the VBE (ALT+F11) then opening the Object Browser (F2) and try a search.

I think you'll find the result is 'No items found'.

I realise this isn't really positive but I also really think we need more information from the OP.

 

[Sal Paradise]

I understand that the code won't work -- I did a quick search on google to see if someone had solved it, and pointed out where I had pasted it from. The point that I'm making is that the logic is probably sound. CLEAN() will not solve the problem of non-ascii characters.

 

[Blade Hunter]

Hi all,

Can any one tell me how to search for Non-ASCII characters in EXCEL?


Thanks in advance!

Nanda

What do you want the code to do with them when it finds them? Remove them or highlight them?

You could set up a loop from 1 to 255 and if the point in the loop falls outside 48 - 122 then you can replace that char with ""

Only a few lines of code along the lines of:

Sub RemoveNoneStandard()
For X = 1 To 255
    If X < 48 Or X > 122 Then
        ActiveCell.Formula = Replace(ActiveCell.Text, Chr(X), "")
    End If
Next
End Sub

You could then of course loop this through your values replacing activecell with the reference.

Cheers

Dan