If you often have to translate the Cyrillic alphabet into transliteration (and try to do it for the surname “Krzhizhanovsky”, for example), then this function is for you.
Open the Visual Basic Editor (Alt + F11), insert a new empty POU via the menu (Insert – Module) and copy the text of this function there:
Function Translit(Txt As String) As String Dim Rus As Variant Rus = Array("a", "b", "c", "d", "e", "e", "e", "g", " h", "i", "d", "k", _ "l", "m", "n", "o", "p", "p", "s", "t", "y" ", "f", "x", "ts", "h", "sh", _ "u", "b", "s", "b", "e", "yu", "i" , "A", "B", "C", "D", "D", "E", _ "E", "F", "Z", "I", "Y", "K", "L", "M", "N", "O", "P", "R", _ "S", "T", "U", "F", "X", "C", " H", "W", "W", "b", "S", "b", "E", "U", "I") Dim Eng As Variant Eng = Array("a", "b" , "v", "g", "d", "e", "jo", "zh", "z", "i", "j", _ "k", "l", "m", "n", "o", "p", "r", "s", "t", "u", "f", "kh", "ts", "ch", _ "sh", " sch", "''", "y", "'", "e", "yu", "ya", "A", "B", "V", "G", "D", _" E", "JO", "ZH", "Z", "I", "J", "K", "L", "M", "N", "O", "P", "R" , _ "S", "T", "U", "F", "KH", "TS", "CH", "SH", "SCH", "''", "Y", "'" , "E", "YU", "YA") For I = 1 To Len(Txt) c = Mid(Txt, I, 1) flag = 0 For J = 0 To 65 If Rus(J) = c Then outchr = Eng(J) flag = 1 Exit For End If Next J If flag Then outstr = outstr & outchr Else outstr = outstr & with N ext I Translit = outstr End Function Now, on any sheet of this book, you can use this function by pasting it through the Function Wizard:
- in Excel 2003 and older – through the menu Insert – Function (Insert — Function)
- in Excel 2007 and newer – through the tab Insert – Function (Insert — Function)
from category User Defined (User defined):
In this macro, options for replacing letters with English equivalents are presented according to GOST. If your task requires other versions (for example, the “i” should be displayed as “ja”, not as “ya”, etc.), then you can freely correct this in the body of the macro. As you might guess, the first two arrays Rus and Eng encode these substitutions.
For new versions of Excel 2007-2010 etc. don’t forget to save your workbook as a macro-enabled file (Macro Enabled Workbook), i.e. in XLSM format.
- Macro translator
- Convert text to transliteration with the PLEX add-on