Search for Latin letters in Cyrillic text and vice versa

Description

It is often necessary to encounter Latin (English) letters in a text written in Cyrillic (Ukrainian or Russian). This often happens during the analysis and processing of text information from databases, where, in turn, text was entered by operators who did not have time to switch to the new keyboard layout.

So we need a mechanism to find Cyrillic in Latin or, conversely, to find Latin in Cyrillic. Excel has no standard tools for such purposes. Below you will find a ready-made macro on VBA, which searches for Latin letters in the text written in Cyrillic and vice versa.

You must add the VBA code to your workbook before using this macro. For this:

1. Press ALT + F11 to open the Visual Basic editor
2. Add a new empty module via the menu Insert - Module
3. Copy and paste the macro code into the module.
4. Save the file and return to Excel.

Now you can select a range of cells, run a macro, and thus check the text for foreign characters.

Search for the Latin alphabet in Cyrillic

Macro code for searching Latin letters:

Sub ShowLatin()
  'moonexcel.com.ua
   Dim c As Range, i As Long
   
   For Each c In Selection
     For i = 1 To Len(c)
       If Mid$(c, i, 1) Like "[A-Za-z]" Then c.Characters(Start:=i, Length:=1).Font.ColorIndex = 3
   Next i, c
End Sub

The result of checking the text in Latin:

Search for Cyrillic in the Latin alphabet

Macro code for searching Cyrillic letters:

Sub ShowCyrylic()
  'moonexcel.com.ua
   Dim c As Range, i As Long
   
   For Each c In Selection
     For i = 1 To Len(c)
       If Mid$(c, i, 1) Like "[А-Яа-я]" Then c.Characters(Start:=i, Length:=1).Font.ColorIndex = 3
   Next i, c
End Sub

The result of checking the Cyrillic text:

Related Articles: