Contents
One of the typical situations that we all face once: someone (perhaps even ourselves), when typing text information into a cell, accidentally used English letters (Latin) instead of (Cyrillic). It’s easy enough to make this mistake, especially given the intricate arrangement of some characters in the standard keyboard layout. Well, what kind of idiot, tell me, came up with the idea to put two letters of the same spelling on one key (English “si” and “es”)? And with other characters it is not better. But, as they say, “minced meat cannot be turned back” – the current layout has long become the standard and there is no getting away from it.
The use of Latin characters in text creates a huge number of problems. From confusion with banal alphabetical sorting to incorrect data consolidation when several tables are automatically merged into one.
It is extremely dreary to look for characters that look similar in appearance and check whether they are characters of the English layout. Therefore, we will look for more elegant options …
Method 1. Font without Cyrillic
Select a range of cells with the text to be checked and temporarily set for it any font that does not contain Cyrillic, for example Albertus or any similar one (found by typing). The appearance of Cyrillic and Latin characters will become different and it will be easy to visually localize incorrect characters:
Method 2. IsLatin function in VBA
Let’s create a custom function (let’s call it, for example, IsLatin), which will check whether the given cell contains English characters and return the boolean value TRUE or FALSE as a result.
Open the Visual Basic editor with the keyboard shortcut ALT + F11 or in older versions of Excel – through the menu Service – Macro – Visual Basic Editor (Tools — Macro — Visual Basic Editor), insert a new module (menu Insert – Module) and copy the text of this function there:
Public Function IsLatin(str As String) str = LCase(str) LatinAlphbet = "*[abcdefghijklmnopqrstuvwxyz]*" If str Like LatinAlphbet Then IsLatin = True Else IsLatin = False End If End Function
Close the Visual Basic Editor and return to Excel.
Now in Function Wizard in category User Defined (User Defined) you can find our function IsLatin and take advantage of it. The function syntax is as follows:
=IsLatin(A2)
where for example A2 is the address of the cell containing the text
The function will return the TRUE value if it finds at least one Latin character in the A2 text. Otherwise, the function will return FALSE:
Method 3. Highlighting Latin characters in red font color
Open the Visual Basic editor with the keyboard shortcut ALT + F11 or in older versions of Excel – through the menu Service – Macro – Visual Basic Editor (Tools — Macro — Visual Basic Editor), insert a new module (menu Insert – Module) and copy the text of this macro there:
Sub ShowLatin() For Each c In Selection For i = 1 To Len(c) If (Asc(Mid(c, i, 1)) >= 65 And Asc(Mid(c, i, 1)) = 90 And Asc(Mid(c, i, 1)) <= 97) Then c.Characters(Start:=i, Length:=1).Font.ColorIndex = 122 End If Next i Next c End Sub
Close the Visual Basic Editor and return to Excel. If we now select the range of cells of interest (but not the entire column or sheet - otherwise it will count until the end of the day!) And run our macro using the ALT + F8 keyboard shortcut or through the menu Service - Macro - Macros (Tools - Macro - Macros), then the Latin characters will be highlighted in red font:
- Latin highlighting with PLEX add-on
- What are macros, where to insert macro code, how to use them