Транслитерация в Excel, замена русских букв на английские и наоборот

Перевести кириллицу в транслит в Excel можно формулой из 64 вложенных функций ПОДСТАВИТЬ — каждая заменяет одну русскую букву латинской — или пользовательской функцией =Translit(A2) на VBA: код ниже, а в !SEMTools она уже встроена. Чтобы сразу получить адрес страницы (ЧПУ), добавьте нижний регистр и дефисы: =СТРОЧН(ПОДСТАВИТЬ(Translit(A2);" ";"-")). А транслитерировать столбец прямо на месте, без формул, можно процедурой !SEMTools в пару кликов.

Технически транслитерация — это замена по списку значений: каждый символ меняется на его эквивалент в другом алфавите. Поэтому и способы — те же, что для массовой замены.

Формула транслита на стандартных функциях Excel

Наименее изящное, тем не менее, самое простое решение задачи. В Excel для замены символов есть функция ПОДСТАВИТЬ (англ. SUBSTITUTE). Формула принимает на вход 4 аргумента:

1. Текст для обработки, в котором будет производиться замена (текст);
2. Заменяемый фрагмент (стар_текст);
3. На что заменить (нов_текст);
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 на любом компьютере, даже с самыми жесткими политиками защиты данных.
• Они легко изменяются, если нужно переделать правила транслитерации, и довольно просты.
• Транспортабельны, т.е. формулы не "слетят", если их отправить кому-либо и открыть на другом компьютере.

Пользовательская функция транслитерации Translit на 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: он дополнительно убирает знаки препинания, двойные дефисы и лишние символы.

Транслитерация столбца в 2 клика с !SEMTools

Чтобы произвести транслитерацию, достаточно просто выделить столбец с данными и вызвать нужную процедуру. Данные изменятся на месте.

Обратная транслитерация (с английского на русский)

Подробнее читайте в статье: обратная транслитерация.

Заменить русские буквы на похожие английские в Excel и наоборот

Отдельная задача — не транслит, а исправление «смешанных» слов, в которых часть букв набрана в другой раскладке: «Москва» с латинской «a» выглядит так же, но не находится поиском и ломает ВПР. Обычно такие ячейки сначала находят по наличию латиницы в русских словах, а потом заменяют похожие латинские буквы на кириллические вложенными ПОДСТАВИТЬ:

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;"A";"А");"B";"В");"C";"С");"E";"Е");"H";"Н");"K";"К");"M";"М");"O";"О");"P";"Р");"T";"Т");"X";"Х");"a";"а");"c";"с");"e";"е");"o";"о");"p";"р");"x";"х");"y";"у")

Формула меняет 18 латинских букв, которые выглядят как русские, на их кириллических двойников. Для обратной задачи (русские буквы в английских словах) поменяйте местами второй и третий аргументы. Применяйте её только к русскому тексту: английские слова она испортит.

Почему транслит получается не таким, как нужно

• Нужен другой стандарт. Единого транслита нет: в загранпаспортах по правилам МИД Щ пишется как SHCH, Ю — IU, Я — IA, Й — I, а в формуле и функции выше — SCH, YU, YA, Y. Поменяйте пары замен под нужный стандарт — в формуле или в массиве lat в коде VBA.
• Ошибка #ИМЯ? в =Translit(A2). Функция работает только в книге, где вставлен код VBA (файл нужно сохранить как .xlsm), или на компьютере с установленной !SEMTools.
• Формула не вводится: слишком много уровней вложенности. Excel допускает максимум 64 вложенные функции — поэтому формула и пропускает заглавные Ъ и Ь. Если добавляете свои замены, уберите столько же других или используйте файл-шаблон с таблицей замен.
• Буквы не заменились. В тексте латинские двойники русских букв («Мocква» с латинской «o») — ПОДСТАВИТЬ ищет русскую «о» и не находит. Сначала замените похожие буквы формулой из раздела выше.

Транслитерация в Excel — частые вопросы

Как перевести кириллицу в латиницу в Excel?

Формулой из вложенных ПОДСТАВИТЬ из этой статьи, пользовательской функцией =Translit(A2) на VBA или процедурой !SEMTools, которая транслитерирует выделенные ячейки на месте.

Есть ли в Excel встроенная функция транслитерации?

Нет. Нужна формула из вложенных ПОДСТАВИТЬ, макрос VBA или надстройка — в !SEMTools функция Translit работает как обычная функция листа.

Как сделать из транслита адрес страницы (ЧПУ)?

Переведите текст в нижний регистр и замените пробелы дефисами: =СТРОЧН(ПОДСТАВИТЬ(Translit(A2);" ";"-")). Для полной очистки от знаков препинания удобнее генератор ЧПУ.

Как заменить латинские буквы, похожие на русские?

Вложенными ПОДСТАВИТЬ для 18 пар букв (A→А, B→В, C→С, E→Е, H→Н, K→К, M→М, O→О, P→Р, T→Т, X→Х и строчных) — формула приведена выше.

Итоги: какой способ транслитерации выбрать

Надеюсь, на этой странице вы нашли то, что искали, и смогли перевести текст в транслит. Формула из ПОДСТАВИТЬ сработает на любом компьютере без макросов, функция VBA короче и легко меняется под стандарт, а для регулярной работы быстрее процедура прямо на месте. Вас наверняка заинтересуют и похожие операции: обратная транслитерация с английского на русский и изменение регистра букв.