
Как перевести кириллицу в транслит в Excel?
Технически транслитерация — это замена по списку значений — символов на определенные их эквиваленты в другой раскладке. Значит, и задача состоит в том, чтобы в Excel осуществить эту серию замен.
Удивительно, но сделать такую массовую замену и транслитерировать текст в Excel можно множеством способов. Обо всех подробнее далее.
Формула транслита с помощью стандартных функций Excel
Наименее изящное, тем не менее, самое простое решение задачи. В Excel для замены символов есть функция ПОДСТАВИТЬ (англ. SUBSTITUTE). Формула принимает на вход 4 аргумента:
- Текст для обработки, в котором будет производиться замена (текст);
- Заменяемый фрагмент (стар_текст);
- На что заменить (нов_текст);
- Какой по счету встреченный в значении фрагмент заменить. (номер_вхождения).

Четвертый параметр является опциональным и, если он не заполнен, заменяются все встреченные в тексте фрагменты.
Как раз то, что нам нужно! Поскольку Excel позволяет составлять сложные вложенные функции, мы можем сделать эту серию замен в одной много раз вложенной формуле.
Но есть несколько проблем, которые в сочетании не дают возможность сделать идеальную единую формулу транслита. Вот они:
- В русском алфавите 33 буквы.
- Идеальная формула транслита должна сохранять при замене регистр символов. Благо, функция «ПОДСТАВИТЬ» — регистрозависимая. Получается, что нужно делать не 33, а 66 замен. Но
- в Excel есть ограничение количества уровней вложенности в одной формуле — 64.
Будь в русском алфавите хотя бы на одну букву меньше, мы бы уложились в ограничения Excel. Есть ли решение? Да, довольно простое.
Формула на 64 уровня вложенности без заглавных Ъ и Ь
Как правило, заглавные буквы в словах бывают, если это начало слова. Или если это аббревиатура, но аббревиатуры тоже составляются из первых букв слов.
А вы знаете слова, начинающиеся с Ъ или Ь? :)
Кажется, их можно пропустить. Так мы экономим два уровня вложенности и укладываемся в лимиты Excel (66 — 2 = 64)!
Важно также учитывать, что при транслитерации заглавных букв Ж, Ш, Ч, Щ и т.д., когда английский эквивалент состоит из нескольких букв, заглавной на латинице должна быть только первая. Интернет полон решениями, где этот момент не учтен. Итак, вот наиболее полная корректная формула:
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(
A1;
"А";"A");"Б";"B");"В";"V");"Г";"G");"Д";"D");"Е";"E");"Ё";"Yo");"Ж";"Zh");"З";"Z");"И";"I");"Й";"Y");
"К";"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");"ё";"yo");"ж";"zh");"з";"z");"и";"i");"й";"y");"к";"k");
"л";"l");"м";"m");"н";"n");"о";"o");"п";"p");"р";"r");"с";"s");"т";"t");"у";"u");"ф";"f");"х";"kh");
"ц";"ts");"ч";"ch");"ш";"sh");"щ";"sch");"ъ";"y");"ы";"y");"ь";"");"э";"e");"ю";"yu");"я";"ya")
Скачать файл-шаблон с транслитерацией
Формула выше на основе функции ПОДСТАВИТЬ с несколькими условиями может быть видоизменена — вместо явного прописывания текстовых значений, можно сделать ее на основе конкретных ячеек. Чем удобнее такой подход? Тем, что формулу менять не нужно, а данные для замены представлены в удобном для восприятия и редактирования формате в столбцах A и B.

По ссылке можно скачать файл-шаблон, в котором применены эти функции.
Формулы транслита, итоги
У подхода с созданием таких мега формул есть свои плюсы и минусы. Минус один — они громоздки и такое не так-то просто сходу набрать. Поэтому их нужно где-то хранить и копировать при необходимости.
Плюсы в основном происходят из недостатков VBA-кода:
- Формулы листа железно сработают в любой версии Excel на любом компьютере, даже с самыми жесткими политиками защиты данных.
- Они легко изменяются, если нужно переделать правила транслитерации, и довольно просты.
- Транспортабельны, т.е. формулы не «слетят», если их отправить кому-либо и открыть на другом компьютере.
Функция на VBA
Код пользовательской функции, которая транслитерирует строчные в строчные, а заглавные — в заглавные, и при этом транслитерирует заглавные буквы Ж, Ш, Х, Ч, Щ и подобные, делая в транслите заглавной только первую букву (Zh, Sh, Kh, Ch, Sch…).
Точь-в-точь эта функция присутствует в надстройке !SEMTools и доступна всем желающим — нужно только скачать и установить !SEMTools. Далее функцию можно применять как обычную функцию Excel на листе. Например, =Translit(A1)
Function Translit(x As String) As String
cyr = "абвгдеёжзийклмнопрстуфхцчшщъыьэюя"
lat = Array("", "a", "b", "v", "g", "d", "e", "e", "zh", "z", "i", "y", "k", _
"l", "m", "n", "o", "p", "r", "s", "t", "u", "f", "kh", "ts", "ch", _
"sh", "sch", "y", "y", "", "e", "yu", "ya")
For i = 1 To 33
x = Replace(x, Mid(cyr, i, 1), lat(i), , , vbBinaryCompare)
x = Replace(x, UCase(Mid(cyr, i, 1)), StrConv(lat(i), vbProperCase), , , vbBinaryCompare)
Next
Translit = x
End Function
Заменить русские буквы на английские в Excel и наоборот
Надстройка !SEMTools предлагает помимо основного еще 4 варианта замены:
- Обратный транслит (из латиницы в кириллицу);
- «Смена раскладки» — asdf -> фыва;
- Замена букв с идентичным начертанием — кириллица в латиницу;
- Аналогичная замена английских букв, похожих на русские, на действительно русские.
Последние два пункта тесно связаны с ситуациями, когда удалось найти русские слова, содержащие латиницу или английские слова с кириллическими символами, и теперь нужно исправить такой текст.
Чтобы произвести транслитерацию, достаточно просто выделить столбец с данными и вызвать нужную процедуру. Данные изменятся на месте.

Нужно сделать транслитерацию русского на латиницу в Excel или наоборот?
Быстро решить эту и более 500 других задач поможет надстройка !SEMTools.
ok
Благодарю за формулу, помогла в решении части задачи!
Всегда пожалуйста!
Большое спасибо за шаблон с формулой! Очень помогло!
Да, там без файла было бы непросто разобраться :)
Спасибо большое! Я формулу применил в google sheets ))
Здорово! Да, практически любые формулы с этого сайта можно использовать и в Google Sheets (Spreadsheets).
Боже, храни того, кто создал шаблон и написал статью. Очень выручили!
Спасибо за теплые слова!
Большое спасибо за помощь
очень полезные советы и особая благодарность за готовый шаблон