Как транслитерировать текст в 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

Поделиться:
27 Комментариев
  • Гриша says:

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

    Dim EngAlphabet As Variant ‘массив из букв английского алфавита
    EngAlphabet = Array(«a», «b», «v», «g», «d», «e», «e», «zh», «z», «y», «i», «k», «l», «m», «n», «o», «p», «r», «s», «t», «u», «f», «h», «tc», «ch», «sh», «shch», «», «y», «», «e», «iu», «ia», «ie», «ii», «A», «B», «V», «G», «D», «E», «Yo», «Zh», «Z», «I», «Y», «K», «L», «M», «N», «O», «P», «R», _
    «S», «T», «U», «F», «H», «Ts», «Ch», «Sh», «Shch», «», «Y», «», «E», «Yu», «Ya», «Ye», «Yi»)

    Dim EngText As String, Letter As String, Flag As Boolean

    For i = 1 To Len(RusText) ‘цикл по всем символам русского текста
    Letter = Mid(RusText, i, 1)
    Flag = 0
    For j = 0 To 68 ‘цикл по всем буквам русского алфавита
    If RusAlphabet(j) = Letter Then ‘если символ из текста совпал с буквой из русского алфавита…
    Flag = 1
    EngText = EngText & EngAlphabet(j) ‘… то добавляем соответствующую букву из английского алфавита
    Exit For
    End If
    Next j
    If Flag = 0 Then EngText = EngText & Letter ‘если символа из текста в алфавите нет (например, знаки препинания и т.п.), то добавляем символ без изменения
    Next i
    Translit = EngText
    End Function

  • Аня says:

    не работает пишет #ИМЯ? — как только я не делала все равно такая ошибка

  • Jamshid says:

    ошибка #ИМЯ?

    • excel says:

      Все пункты из «Важно» выполнили?

  • Мария says:

    Вложение

    Добрый день!
    Подскажите, пожалуста, где ошибка?

    • excel says:

      Что то при копировании не так. Не та кодировка. А вы переключаете на русский язык перед копированием текста. Как написано в инструкции?

  • Гасан says:

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

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

    • excel says:

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

  • Семён says:

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

    • Анатолий says:

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

  • А says:

    Добрый день.
    Спасибо за макрос.
    Вариация на тему:

    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 says:

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

  • Игнат says:

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

  • Анатолий says:

    Решение не элегантное, но решение !
    =Translit(ПОДСТАВИТЬ(B10;»/»;»-«))

  • Анатолий says:

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

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

    • excel says:

      Добрый день! Попробуйте изменить
      For J = 0 To 65
      на
      For J = 0 To 66

  • Алина says:

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

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

    • excel says:

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

  • Иван says:

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

    • excel says:

      Проверил еще раз — оказалось, что действительно не хватало одной строчки кода.
      Поправил код — должно работать.
      Спасибо, что указали на ошибку

  • Иван says:

    ошибка возникает в строке:
    If flag Then outstr = outstr & outchr Else outstr = outstr & с

  • Иван says:

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

    • Иван says:

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

      • excel says:

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

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

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

    • excel says:

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

  • Ольга says:

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

    • excel says:

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

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

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

×
Рекомендуем посмотреть