Highlight/find non-Latin letters

[sevas1994]

Hi all,

Is there any way to get all non-Latin letters highlighted or found using formula/VBA so on?

Thanks in advance

 

[pgc01]

Hi

No, there is no way with a formula.

No, there is no native/simple way with vba.

You'll have to use vba and write some code yourself.
Steps:

- make a list of the letters that you consider valid latin letters
- loop through the text in the cells and check each character to see if it's part of that list
- when you find a character that is not part of the list highlight it


Remark: This can only be done for cells with constant text. In case the text in the cell is the result of a formula this is simply not possible.

 

[sevas1994]

Many thanks for the answer

Hi

No, there is no way with a formula.

No, there is no native/simple way with vba.

You'll have to use vba and write some code yourself.
Steps:

- make a list of the letters that you consider valid latin letters
- loop through the text in the cells and check each character to see if it's part of that list
- when you find a character that is not part of the list highlight it


Remark: This can only be done for cells with constant text. In case the text in the cell is the result of a formula this is simply not possible.

 

[pgc01]

You're welcome.

Remark:

If you want to write a general macro to do it, you'll need a list with the all the characters.
The Latin characters are used in many languages and they sometimes have diacritics, the list is quite long (more than 500).

I've extracted the characters of the Latin alphabet from the Unicode map and this will save you some (a lot of ) time.

I've posted it here in post #14:

http://www.mrexcel.com/forum/excel-questions/348352-convert-symbols-letters.html#14

 

[pgc01]

P. S. You have to add to the list, of course, the basic 52 Latin characters a-z A-Z

 

[sevas1994]

Wow thanks. Quite a list to analyze

You're welcome.

Remark:

If you want to write a general macro to do it, you'll need a list with the all the characters.
The Latin characters are used in many languages and they sometimes have diacritics, the list is quite long (more than 500).

I've extracted the characters of the Latin alphabet from the Unicode map and this will save you some (a lot of ) time.

I've posted it here in post #14:

http://www.mrexcel.com/forum/excel-questions/348352-convert-symbols-letters.html#14

 

[István Hirsch]

Do you want to highlight all the characters which are not in the English alphabet (A-Za-z)?

 

[sevas1994]

Yes. All of them.

Do you want to highlight all the characters which are not in the English alphabet (A-Za-z)?

 

[István Hirsch]

Yes. All of them.

*	A	B
gsdf sdBEROÉÁERf sf ßsdf$ $ & ért zázáh oloer	gsdf sdBEROÉÁERf sf ßsdf$ $ & ért zázáh oloer
AFTER MACRO	BEFORE MACRO

<colgroup><col style="width:30px; "><col style="width:300px;"><col style="width:298px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Give this VBA a try:

Sub NonLatin()
Dim cell As Range
    For Each cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
        s = cell.Value
            For i = 1 To Len(s)
                If Mid(s, i, 1) Like "[!A-Za-z ]" Then cell.Characters(i, 1).Font.Bold = True
            Next
    Next
End Sub

I have used bold instead of highlight as I do not know how to highlight a character with a VBA.

The data should be in column A.

Note: the difference is not visible on this page, but in A1 the non-English characters are bold in my spreadsheet.

 

[sevas1994]

Many thanks

*	A	B
gsdf sdBEROÉÁERf sf ßsdf$ $ & ért zázáh oloer	gsdf sdBEROÉÁERf sf ßsdf$ $ & ért zázáh oloer
AFTER MACRO	BEFORE MACRO

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Give this VBA a try:

Sub NonLatin()
Dim cell As Range
    For Each cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
        s = cell.Value
            For i = 1 To Len(s)
                If Mid(s, i, 1) Like "[!A-Za-z ]" Then cell.Characters(i, 1).Font.Bold = True
            Next
    Next
End Sub

I have used bold instead of highlight as I do not know how to highlight a character with a VBA.

The data should be in column A.

Note: the difference is not visible on this page, but in A1 the non-English characters are bold in my spreadsheet.