Как транслитерировать текст в Excel (русский текст преобразовать в английский транслит)

Очень часто требуется преобразовать русский текст (кириллицу) в английский (транслит), не перевести, а написать русские слова английскими буквами.

Такое, например, может понадобиться для импорта данных в какую-нибудь программу, базу данных, которая не поддерживает русские буквы. Задача распространенная, но стандартными средствами Excel не решается.

как преобразовать английй текст в транслит в Excel

Мы предлагаем вам два варианта решения данной задачи.

Первый способ транслитерировать русский текст

Первый способ транслита русского текста достаточно топорный — с использованием приложения Punto Switcher от Яндекс.

Скачиваем программу Punto Switcher на официальном сайте: http://punto.yandex.ru/

Punto Switcher — программа, которая автоматически переключает раскладку клавиатуры. Она знает, что «ghbdtn» — это «привет», а «пщщв ьщктштп» — это «good morning». 

Так вот, данная программа, помимо своей основной функции можете переводить текст в транслит, менять регистр выделенного текста (сделать из «иванов» в «ИВАНОВ») и другое.

Устанавливаем программу себе на компьютер, в системном трее должен появится значок программы

punto-switcher

Если зайти в настройки (правой кнопкой мыши по значку, выбрать настройки, горячие клавиши), то можно увидеть, что по умолчанию установлено переводить текст в транслит с помощью горячих клавиш Alt+Scroll Lock

Горячие клавиши в Punto Switcher

К сожалению, в самом Excel программа транслитерировать текст не сможет, необходимо промежуточное действие. Копируем столбец с русскими буквами, открываем блокнот на компьютере (Notepad) и вставляем туда текст, далее выделяем текст в блокноте и нажимаем клавиши Alt+Scroll Lock, текст автоматически поменяется на транслит.

Далее вы просто копируете этот текст и вставляете обратно в Excel.

Это удобно если нужно например перевести в translit, например один объемный столбец данных на русском языке, но иногда преобразование текста в транслит требуется с помощью формул Excel, поэтому рассмотрим второй способ с использованием пользовательской функции Excel, написанной на VBA

Второй способ перевода русского текста в транслит в Excel с помощью пользовательской функции

Есть специальный макрос для создания пользовательской функции.

Function Translit(Txt As String) As String

 Dim Rus As Variant
 Rus = Array("а", "б", "в", "г", "д", "е", "ё", "ж", "з", "и", "й", "к", _
 "л", "м", "н", "о", "п", "р", "с", "т", "у", "ф", "х", "ц", "ч", "ш", _
 "щ", "ъ", "ы", "ь", "э", "ю", "я", "А", "Б", "В", "Г", "Д", "Е", _
 "Ё", "Ж", "З", "И", "Й", "К", "Л", "М", "Н", "О", "П", "Р", _
 "С", "Т", "У", "Ф", "Х", "Ц", "Ч", "Ш", "Щ", "Ъ", "Ы", "Ь", "Э", "Ю", "Я")

 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)
 с = Mid(Txt, I, 1)

 flag = 0
 For J = 0 To 65
 If Rus(J) = с Then
 outchr = Eng(J)
 flag = 1
 Exit For
 End If
 Next J
If flag Then outstr = outstr & outchr Else outstr = outstr & с
 Next I

 Translit = outstr
End Function

Переключите раскладку на русский язык!! Копируем данный код, открываем среду VBA, для это нажмите правой кнопкой на активный лист и выберите пункт «Исходные данные», либо нажмите Alt+F11 далее через меню выберите новый программный модуль (Insert — Module)

Открываем среду VBA для вставки кода

и в открывшееся окно вставляете данный код и закрываете это окно.

После этого в любом месте данного листа (а при вставке кода с помощью Insert — Module — в любом месте данной книги) вы сможете использовать пользовательскую функцию, которую можно вызвать выбрав:

Вставка — Функция (Insert — Function)

в Excel 2013 Функция — Вставить функцию

Выбираем Категория: определенные пользователем, в данном списке должна появится наша функция Translit

функция транслит

Можно просто в нужной ячейке набрать =Transit (ссылка_на_русский_текст) 

Как преобразовать тект в транслит - функция Excel

После этого протягиваем формулу и получаем нужный нам результат.

Если вы присмотритесь в полученный результат, то увидите, что данные немного отличаются, например

Ivanov Sergej Petrovich и Ivanov Sergey Petrovich

то есть буква «й» преобразовалась по-разному, если для вас это критично, то нужно будет немного поменять указанный выше код. В коде есть две таблицы — одна на русском языке, другая латиницей. Не трудно догадаться, что вам необходимо поменять нужные вам буквы. В данном случае в коде нужно найти «j» и поменять на «y», если требуется, то по аналогии поступаем с другими буквам.

Важно!

  • Начиная с Excel 2007 не забудьте сохранить файл в формате поддержки макросов — xlsm, иначе макрос не сохранится;
  • Данный макрос необходимо вставлять в каждую отдельную книгу, в которой вы хотите, чтобы он работал;
  • Иногда требуется, чтобы была не встроенная функция, а макрос при запуске которого текст будет переводится в транслит, в этом случае нужно вставить код, указанный ниже. В итоге, для перевода текста в transit необходимо выделить нужный текст на русском языке (диапазон ячеек) и запустить макрос «Translit» и данный текст будет обработан.
  • Важно! Перед копированием данного кода, обязательно переключите раскладку клавиатуры на русский язык.
Sub Translit()
Dim Txt As Variant
Dim i As Integer
Dim J As Integer
Dim c As String
Dim flag As Integer
Dim outchr As String
Dim outstr As String

 Dim Rus As Variant
 Rus = Array("а", "б", "в", "г", "д", "е", "ё", "ж", "з", "и", "й", "к", _
 "л", "м", "н", "о", "п", "р", "с", "т", "у", "ф", "х", "ц", "ч", "ш", _
 "щ", "ъ", "ы", "ь", "э", "ю", "я", "А", "Б", "В", "Г", "Д", "Е", _
 "Ё", "Ж", "З", "И", "Й", "К", "Л", "М", "Н", "О", "П", "Р", _
 "С", "Т", "У", "Ф", "Х", "Ц", "Ч", "Ш", "Щ", "Ъ", "Ы", "Ь", "Э", "Ю", "Я")

 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 Each Txt In Selection.Cells
 outstr = Empty
 For i = 1 To Len(Txt)
 с = Mid(Txt, i, 1)

 flag = 0
 For J = 0 To 65
 If Rus(J) = с Then
 outchr = Eng(J)
 flag = 1
 Exit For
 End If
 Next J
 If flag Then outstr = outstr & outchr Else outstr = outstr & с
 Next i
 Txt.Value = outstr
 Next Txt
End Sub
 

Итак, надеюсь вы нашли ответ на свой вопрос о том, как проще всего транслитерировать текст в Excel (преобразовать текст на русском языке в транслит).

Ставьте лайки, подписывайтесь на наши группы в социальных сетях и следите за следующими уроками.

SirExcel — безграничные возможности Excel

Поделиться:
21 Комментарий
  • Гасан:

    вложение  01.2016-1.xls

    как изменить шрифт с кириллицы на латинский в эксель готовом тексте?

    • excel:

      про это написано в статье

  • Семён:

    Подскажите выскакивает ошибка joxi.ru/EA4bKqJhDMXEKm

    • Анатолий:

      Расставьте строки так же, как указано в примере, а не в одну строку.

      Visual Basic ругается, когда не соблюдаются переносы строк, поскольку конец строки подразумевает конец выполнения одной отдельной операции.

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

  • А:

    Добрый день.

    Спасибо за макрос.

    Вариация на тему:

    Function Translit (Txt As String) As String

    a = Array («а», «a», «б», «b», «в», «v», «г», «g», «д», «d», «е», «e», «ё», «jo», «ж», «zh», «з», «z», «и», «i», «й», «j», «к», «k», «л», «l», «м», «m», «н», «n», «о», «o», «п», «p», «р», «r», «с», «s», «т», «t», «у», «u», «ф», «f», «х», «h», «ц», «c», «ч», «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», «Х», «H», «Ц», «C», «Ч», «CH», «Ш», «SH», «Щ», «SCH», «Ъ», "'' ", «Ы», «Y», «Ь», "' ", «Э», «E», «Ю», «YU», «Я», «YA»)

    For i = 0 To 121 Step 2

    Txt = Replace (Txt, a (i), a (i + 1))

    Next i

    Translit = Txt

    End Function

  • excel:

    У меня не ругается, если вы добавляете знак, а не меняете, то не забудьте увеличить кол-во итераций. For J = 0 To 65 поменять на For J = 0 To 66

    При этом кавычки нужно вставить именно елочки

  • Игнат:

    Подскажите пожалуйста. Хочу транслитерировать знак ковычек " в маленькую латинскую m. Какой знако поставить в ковычках? на " ругается ошибкой синтаксиса

  • Анатолий:

    Решение не элегантное, но решение !

    =Translit (ПОДСТАВИТЬ(B10; «/»; «-»))

  • Анатолий:

    Добрый день, функция работает отлично !

    Хотелось бы чтобы ещё слэш менялся на тире попробовал дописать, «/» и, «-» не работает, что изменить подскажите пожалуйста. Заранее спасибо.

    • excel:

      Добрый день! Попробуйте изменить

      For J = 0 To 65

      на

      For J = 0 To 66

  • Алина:

    отличный транслит, подскажите, пожалуйста, что поменять в коде, чтобы перевести текст на транслите на русский — обратный перевод. Что, помимо того, что поменять алфавит и обозначения нужно сделать.

    Заранее огромное спасибо.

    • excel:

      Этого недостаточно, там нужно будет еще код дописать. Макрос ведь берет по одной букве и соответствующий ей транслит, например находит одну русскую букву «ш» и переводит ее в две буквы «sh».

      Если просто буквы поменять местами, то буква «sh» не измениться на «ш» а поменяется на как буква «s», поэтому нужно будет дописывать, чтобы макрос сначала проверял по две буквы и сначала менял их, а потом уже прогонял по одной букве. К сожалению в написании макроса не помогу

  • Иван:

    После исправления (удаления ;amp) работает, но нужно добавить ": " после Else в строке 29. Кроме того, подпрограмма не правильно работает при выделении столбца. В каждой последующей строке появляется результат предыдущей строки + результат текущей строки, так как это описала Ольга (то есть A, A&B, A&B&C….)

    • excel:

      Проверил еще раз — оказалось, что действительно не хватало одной строчки кода.

      Поправил код — должно работать.

      Спасибо, что указали на ошибку

  • Иван:

    ошибка возникает в строке:

    If flag Then outstr = outstr & outchr Else outstr = outstr & с

  • Иван:

    Не работает! ошибки: outchr : : Empty

    outstr : 

    • Иван:

      В каком случае использовать подпрограмму Sub Translit, а в каком случае Function Translit. Ни та ни другая не работают.При отладке выдается ошибка: Нет определения outstr и outchr

      • excel:

        Действительно код сбился при сохранении. Пробуйте еще раз, должно работать. Перед копирование переключите раскладку на русский язык.

        Sub Translit — используются, когда вам нужно выделить нужный текст, запустить макрос и преобразовать текст.

        Function Translit — будет работать как отдельная функция. Например написан текст русскими буквами, а рядом вы прописываете формул транслита и он будет отображаться английскими буквами.

    • excel:

      Должно работать, недавно проверял.

      Обратите внимание на вот этот пункт:

      "Важно! Перед копированием данного кода, обязательно переключите раскладку клавиатуры на русский язык. "

  • Ольга:

    Добрый день!

    Подскажите, почему, если выделить несколько ячеек и запустить макрос, значение в каждой последующей состоит из предыдущего и текущего значений? (то есть A, A&B, A&B&C...)

    • excel:

      Добрый день! Сейчас проверил, все работает как часы. Единственное, когда копируете код макроса в VBA, перед тем как копировать код макроса, нужно обязательно переключиться на русскую клавиатура, тогда код скопируется в правильной кодировке

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

Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.