Как транслитерировать текст в 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 с помощью пользовательской функции
Есть специальный макрос для создания пользовательской функции.
Переключите раскладку на русский язык!! Копируем данный код, открываем среду VBA, для это нажмите правой кнопкой на активный лист и выберите пункт «Исходные данные«, либо нажмите Alt+F11 далее через меню выберите новый программный модуль (Insert — Module)
и в открывшееся окно вставляете данный код и закрываете это окно.
После этого в любом месте данного листа (а при вставке кода с помощью Insert — Module — в любом месте данной книги) вы сможете использовать пользовательскую функцию, которую можно вызвать выбрав:
Вставка — Функция ( Insert — Function )
в Excel 2013 Функция — Вставить функцию
Выбираем Категория: определенные пользователем, в данном списке должна появится наша функция Translit
Можно просто в нужной ячейке набрать =Transit(ссылка_на_русский_текст)
После этого протягиваем формулу и получаем нужный нам результат.
Если вы присмотритесь в полученный результат, то увидите, что данные немного отличаются, например
Ivanov Serge j Petrovich и Ivanov Serge y Petro vich
то есть буква «й» преобразовалась по-разному, если для вас это критично, то нужно будет немного поменять указанный выше код. В коде есть две таблицы — одна на русском языке, другая латиницей. Не трудно догадаться, что вам необходимо поменять нужные вам буквы. В данном случае в коде нужно найти «j» и поменять на «y», если требуется, то по аналогии поступаем с другими буквам.
- Начиная с Excel 2007 не забудьте сохранить файл в формате поддержки макросов — xlsm, иначе макрос не сохранится;
- Данный макрос необходимо вставлять в каждую отдельную книгу, в которой вы хотите, чтобы он работал;
- Иногда требуется, чтобы была не встроенная функция, а макрос при запуске которого текст будет переводится в транслит, в этом случае нужно вставить код, указанный ниже. В итоге, для перевода текста в transit необходимо выделить нужный текст на русском языке (диапазон ячеек) и запустить макрос «Translit» и данный текст будет обработан.
- Важно! Перед копированием данного кода, обязательно переключите раскладку клавиатуры на русский язык.
Итак, надеюсь вы нашли ответ на свой вопрос о том, как проще всего транслитерировать текст в Excel (преобразовать текст на русском языке в транслит).
Ставьте лайки, подписывайтесь на наши группы в социальных сетях и следите за следующими уроками.
Транслит
Если Вам часто приходится переводить кириллицу в транслит (а попробуйте-ка сделать это для фамилии "Кржижановский", например), то эта функция — для Вас.
Откройте редактор Visual Basic (Alt+F11), вставьте через меню новый пустой программный модуль (Insert — Module) и скопируйте туда текст этой функции:
Теперь на любом листе этой книги Вы можете использовать эту функцию, вставив ее через Мастер Функций:
из категории Определенные пользователем (User defined) :
В этом макросе варианты замены русских букв английскими эквивалентами представлены согласно ГОСТ. Если для вашей задачи нужны другие версии (например, русская "я" должна выводиться как "ja", а не как "ya" и т.д.), то можно свободно подкорректировать это в теле макроса. Как легко догадаться, два первых массива Rus и Eng как раз и кодируют эти подстановки.
Для новых версий Excel 2007-2010 и т.д. не забудьте сохранить вашу книгу как файл с поддержкой макросов (Macro Enabled Workbook) , т.е. в формате XLSM.
Как правильно настроить поиск и замену английских букв в русском тексте таблиц Excel
Одна из типовых ситуаций, с которой все мы однажды сталкиваемся, выглядит так: кто-то при наборе текстовой информации в ячейку случайно использовал английские буквы (латиницу) вместо русских (кириллицы). Допустить такую ошибку легко даже самим, особенно если учесть изощренное расположение некоторых символов (русская «С» и английская «С», например) в стандартной раскладке клавиатуры.
Использование символов латиницы в русском тексте порождает огромное количество проблем. От путаницы при банальной сортировке по алфавиту до некорректной консолидации данных при автоматическом объединении нескольких таблиц в одну. Выискивать похожие по виду символы и проверять, не являются ли они символами английской раскладки, крайне мучительно. Поэтому имеет смысл рассмотреть более изящные варианты решения этой задачи.
Способ 1. Шрифт без кириллицы
Выделите диапазон ячеек с проверяемым текстом и временно установите для него любой шрифт, не содержащий кириллицу, например Albertus, Bauhaus93 или любой аналогичный (находится методом перебора). Внешний вид символов кириллицы и латиницы станет отличаться, и можно будет легко визуально локализовать некорректные символы.
Шрифт без кириллицы
Способ 2. Подсветка латиницы красным цветом шрифта
Этот способ является продолжением и развитием предыдущей идеи и заключается в изменении цвета символов латиницы на красный с помощью небольшого макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert – Module) и введите туда следующий код.
Sub ShowLatinRed() For Each c In Selection For i = 1 To Len(c) If (Asc(Mid(c, i, 1)) >= 65 And Asc(Mid(c, i, 1)) <= 90) Or _ (Asc(Mid(c, i, 1)) >= 97 And Asc(Mid(c, i, 1)) <= 122) Then c.Characters(Start:=i, Length:=1).Font.ColorIndex = 3 End If Next i Next c End Sub
Теперь если выделить интересующий диапазон ячеек и запустить наш макрос с помощью сочетания клавиш Alt+F8 или через вкладку Разработчик → Макросы (Developer → Macros), то символы латиницы выделятся красным цветом шрифта.
Символы латиницы выделятся красным цветом
Способ 3. Функция IsLatin на VBA
Если количество проверяемых ячеек велико, то визуальное определение станет затруднительным. Для таких случаев можно создать пользовательскую функцию (назовем ее, например, IsLatin), которая будет проверять, присутствуют ли в заданной ячейке символы английского алфавита, и выдавать в качестве результата логическое значение ИСТИНА или ЛОЖЬ.
Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert → Module) и введите туда текст этой небольшой функции.
Function IsLatin(str As String) as Boolean str = LCase(str) LatinAlphbet = "*[abcdefghijklmnopqrstuvwxyz]*" If str Like LatinAlphbet Then IsLatin = True Else IsLatin = False End If End Function
Как можно заметить, макрофункция использует интересный оператор Like языка Visual Basic, который проверяет наличие в исходном тексте любых символов из английского алфавита, предварительно преобразовав текст в нижний регистр с помощью функции LCase.
Теперь в Мастере функций в категории Определенные пользователем (User Defined) можно найти нашу функцию IsLatin и воспользоваться ею. Функция выдаст значение ИСТИНА (TRUE), если найдет в тексте аргумента хотя бы один символ латиницы. В противном случае функция вернет значение ЛОЖЬ (FALSE).
Функция выдаст значение ИСТИНА или ЛОЖЬ
Замена латиницы на кириллицу
Если необходимо не просто обнаружить чужеродные английские буквы в русском массиве текста, а исправить символы латиницы на соответствующую им кириллицу, можно быстро сделать это с помощью макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert → Module) и введите туда его текст.
Sub Replace_Latin_to_Russian() Rus = "асекорхуАСЕНКМОРТХ" Eng = "acekopxyACEHKMOPTX" For Each cell In Selection For i = 1 To Len(cell) c1 = Mid(cell, i, 1) If c1 Like "[" & Eng & "]" Then c2 = Mid(Rus, InStr(1, Eng, c1), 1) cell.Value = Replace(cell, c1, c2) End If Next i Next cell End Sub
Теперь если выделить на листе диапазон и запустить наш макрос сочетанием клавиш Alt+F8 или на вкладке Разработчик → Макросы (Developer → Macros), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу.
Небольшой макрос Excel для транслита русских букв
Если вам необходимо выполнить транслит русских букв в английские.
Сохраняем файл excel как Книга Excel с поддержкой макросов (*xlsm) . Вызываем сочетанием клавиш Alt+F11 Microsoft Visual Basic For Applications, в окне Project кликаем правой кнопкой мыши, выбираем Insert — Module и вставляем следующее:
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», «e», «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 = 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
Для транслита русских букв в английские в ячейке вводим: =TRANSLIT(НомерЯчейки)