Как транслитерировать текст в Excel (русский текст преобразовать в английский транслит)
Очень часто требуется преобразовать русский текст (кириллицу) в английский (транслит), не перевести, а написать русские слова английскими буквами.
Такое, например, может понадобиться для импорта данных в какую-нибудь программу, базу данных, которая не поддерживает русские буквы. Задача распространенная, но стандартными средствами Excel не решается.
Мы предлагаем вам два варианта решения данной задачи.
Первый способ транслитерировать русский текст
Первый способ транслита русского текста достаточно топорный — с использованием приложения Punto Switcher от Яндекс.
Скачиваем программу Punto Switcher на официальном сайте: http://punto.yandex.ru/
Punto Switcher — программа, которая автоматически переключает раскладку клавиатуры. Она знает, что «ghbdtn» — это «привет», а «пщщв ьщктштп» — это «good morning».
Так вот, данная программа, помимо своей основной функции можете переводить текст в транслит, менять регистр выделенного текста (сделать из «иванов» в «ИВАНОВ») и другое.
Устанавливаем программу себе на компьютер, в системном трее должен появится значок программы
Если зайти в настройки (правой кнопкой мыши по значку, выбрать настройки, горячие клавиши), то можно увидеть, что по умолчанию установлено переводить текст в транслит с помощью горячих клавиш Alt+Scroll Lock
К сожалению, в самом 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)
и в открывшееся окно вставляете данный код и закрываете это окно.
После этого в любом месте данного листа (а при вставке кода с помощью Insert — Module — в любом месте данной книги) вы сможете использовать пользовательскую функцию, которую можно вызвать выбрав:
Вставка — Функция (Insert — Function)
в Excel 2013 Функция — Вставить функцию
Выбираем Категория: определенные пользователем, в данном списке должна появится наша функция Translit
Можно просто в нужной ячейке набрать =Transit(ссылка_на_русский_текст)
После этого протягиваем формулу и получаем нужный нам результат.
Если вы присмотритесь в полученный результат, то увидите, что данные немного отличаются, например
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
Все работает, спасибо большое!
Вложение excel-error.docx
Добрый день!
Не появляется пользовательская функция, пишет ошибку #NAME?. Подскажите, пожалуйста, в чем ошибка?
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
не работает пишет #ИМЯ? — как только я не делала все равно такая ошибка
ошибка #ИМЯ?
Все пункты из «Важно» выполнили?
Вложение
Добрый день!
Подскажите, пожалуста, где ошибка?
Что то при копировании не так. Не та кодировка. А вы переключаете на русский язык перед копированием текста. Как написано в инструкции?
Вложение 01.2016-1.xls
как изменить шрифт с кириллицы на латинский в эксель готовом тексте?
про это написано в статье
Подскажите выскакивает ошибка http://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
У меня не ругается, если вы добавляете знак, а не меняете, то не забудьте увеличить кол-во итераций. For J = 0 To 65 поменять на For J = 0 To 66
При этом кавычки нужно вставить именно елочки
Подскажите пожалуйста. Хочу транслитерировать знак ковычек » в маленькую латинскую m. Какой знако поставить в ковычках? на » ругается ошибкой синтаксиса
Решение не элегантное, но решение !
=Translit(ПОДСТАВИТЬ(B10;»/»;»-«))
Добрый день, функция работает отлично !
Хотелось бы чтобы ещё слэш менялся на тире попробовал дописать , «/» и , «-» не работает, что изменить подскажите пожалуйста. Заранее спасибо.
Добрый день! Попробуйте изменить
For J = 0 To 65
на
For J = 0 To 66
отличный транслит, подскажите, пожалуйста, что поменять в коде, чтобы перевести текст на транслите на русский — обратный перевод. Что, помимо того, что поменять алфавит и обозначения нужно сделать.
Заранее огромное спасибо.
Этого недостаточно, там нужно будет еще код дописать. Макрос ведь берет по одной букве и соответствующий ей транслит, например находит одну русскую букву «ш» и переводит ее в две буквы «sh».
Если просто буквы поменять местами, то буква «sh» не измениться на «ш» а поменяется на как буква «s», поэтому нужно будет дописывать, чтобы макрос сначала проверял по две буквы и сначала менял их, а потом уже прогонял по одной букве. К сожалению в написании макроса не помогу
После исправления (удаления ;amp) работает, но нужно добавить «:» после Else в строке 29. Кроме того, подпрограмма не правильно работает при выделении столбца. В каждой последующей строке появляется результат предыдущей строки + результат текущей строки, так как это описала Ольга (то есть A, A&B, A&B&C….)
Проверил еще раз — оказалось, что действительно не хватало одной строчки кода.
Поправил код — должно работать.
Спасибо, что указали на ошибку
ошибка возникает в строке:
If flag Then outstr = outstr & outchr Else outstr = outstr & с
Не работает! ошибки: outchr : : Empty
outstr :
В каком случае использовать подпрограмму Sub Translit, а в каком случае Function Translit. Ни та ни другая не работают.При отладке выдается ошибка: Нет определения outstr и outchr
Действительно код сбился при сохранении. Пробуйте еще раз, должно работать. Перед копирование переключите раскладку на русский язык.
Sub Translit — используются, когда вам нужно выделить нужный текст, запустить макрос и преобразовать текст.
Function Translit — будет работать как отдельная функция. Например написан текст русскими буквами, а рядом вы прописываете формул транслита и он будет отображаться английскими буквами.
Должно работать, недавно проверял.
Обратите внимание на вот этот пункт:
«Важно! Перед копированием данного кода, обязательно переключите раскладку клавиатуры на русский язык.»
Добрый день!
Подскажите, почему, если выделить несколько ячеек и запустить макрос, значение в каждой последующей состоит из предыдущего и текущего значений? (то есть A, A&B, A&B&C….)
Добрый день! Сейчас проверил, все работает как часы. Единственное, когда копируете код макроса в VBA, перед тем как копировать код макроса, нужно обязательно переключиться на русскую клавиатура, тогда код скопируется в правильной кодировке