Транслитерация текста в Excel

Разберем пользовательскую функцию для транслитерации текста в Excel из кириллицы (русского текста) на латиницу (английский транслит), к примеру "Слон" в "Slon".

Иногда возникают ситуации, когда нам необходимо преобразовать текст на кириллице в транслит и наоборот.
Если это разовое преобразование, то можно воспользоваться одним из множества сервисов в интернете, однако если перевод носит систематический характер и имеет определенную специфику использования в Excel, то такой вариант уже не подойдет.

Рассмотрим 2 различных способа транслитерации текста:

  • Формула. Транслит текста можно получить с помощью функции ПОДСТАВИТЬ и последовательной замены символов из кириллицы на символ из латиницы (например, замена "б" на "b", "в" на "v", "г" на "g" и т.д.);
  • Пользовательская функция (UDF), макрос. Аналогичная процедура написанная на языке VBA.

В первом случае (вариант формулы) нам придется воспользоваться функцией ПОДСТАВИТЬ 66 раз (для преобразования 33 букв из русского алфавита в нижнем и верхнем регистрах), что делает применение данного варианта неудобным из-за размера применяемой формулы, поэтому подробно остановимся на варианте с пользовательской функцией.

Функция транслита в Excel

Как и всегда при создании пользовательской функции, сначала переходим в редактор Visual Basic (сочетание клавиш Alt + F11), затем создаем новый модуль и вставляем в него следующий код:

Обратите внимание, что в коде макроса мы использовали вариант транслитерации по правилам перевода загранпаспорта.
Если есть необходимость скорректировать какие-либо правила перевода под свои задачи, то это легко можно сделать в теле функции (изменив массив EngAlphabet в строке 6).
Вызвать пользовательскую функцию в Excel мы можем стандартным способом — либо непосредственно ввести формулу в ячейку, либо воспользоваться мастером функций (выбрав из категории Определенные пользователем):

Пример транслитерации текста

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

В случае обратной транслитерации (из латиницы в кириллицу) возникает ряд трудностей, все из которых достаточно тяжело предусмотреть в макросе.
Например, ряд символов "sch" можно интерпретировать и как букву "щ", так и как набор букв "сч", и множество других подобных сочетаний, где нет однозначного соответствия между буквами русского и английского алфавитов.
Поэтому, на мой взгляд, предпочтительнее сделать перевод латиницы в кириллицу вручную во избежание возможных ошибок.

Скачать файл с примером

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Поделиться с друзьями:
Поиск по сайту:

Комментарии (13)

  1. Екатерина

    Добрый день!
    Что делать, если ручном вводе функции TranslitText в итоге выходит #ИМЯ?
    В мастере функций такую формулу найти не получается dthcbz Excei 2013
    Заранее спасибо за помощь

    1. tutorexcel Автор поста

      Если появляется ошибка #ИМЯ, то вероятно в книге просто не включены макросы — попробуйте их включить.

  2. Екатерина

    пожалуйста, напишите точный текст, который нужно вводить в модуль….
    Так,чтобы можно было просто скопировать отсюда и вставить в модуль.
    Спасибо заранее

  3. Витек

    Объясните пошагово как установить данный модуль? Постоянно показывает ошибку.

  4. Игорь

    Благодарю. Но у меня возник вопрос: Мне нужно в одной ячейки данные из трёх.
    То есть в одной ячейке у меня Фамилия, в другой имя, в третьей отчество. Мне нужна ячейка с транслитом из этих трёх с разделителем точкой.
    =TranslitText(B2;C2;D2) выдаёт ошибку #ЗНАЧ
    Подскажите, пожалуйста, как мне это реализовать.

    1. tutorexcel Автор поста

      Игорь, Вы ввели в функцию 3 аргумента, а нужно только 1.
      Вместо =TranslitText(B2;C2;D2) поставьте =TranslitText(B2&C2&D2), тогда данные из 3 ячеек склеятся в 1 (если необходимо, то поставьте в формуле еще разделительные знаки).

  5. Тимур

    У меня почему-то не работает. Выводит то же значение на русском языке, которое было в изначальной ячейке. Скачал пример, тот же эффект. В чем может быть проблема?

    1. tutorexcel Автор поста

      Сложно сказать, если в файле с примером тоже не работает, то возможно дело в настройках системы.
      Если не сложно, то пришлите ваш файл мне на почту (info@tutorexcel.ru), я посмотрю.

  6. Gron

    Добрый день! Подскажите как сделать чтобы заглавные буквы Б, Ю, Ж, Э, Х заменялись на знаки , :, «, { соответственно ?

    1. tutorexcel Автор поста

      В коде макроса можно поменять соответствующие буквы (в массиве RusAlphabet) на нужные знаки (в массиве EngAlphabet) и добавить дополнительную проверку регистра (что это заглавные буквы).

Добавить комментарий

Ваш e-mail не будет опубликован.

Читайте ранее:
Формула номера листа и страницы в Excel

Разберем формулы номеров листов и страниц в Excel (как стандартные, так и пользовательские функции) для создания оглавления и удобной навигации...

Закрыть