Як транслітерувати текст в excel (російський текст перетворити в англійський транслит)

Відео: Переклад російського тексту в трансліт

Дуже часто потрібно перетворити російський текст (кирилицю) в англійську (трансліт), не перевести, а написати російські слова англійськими літерами.

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

Ми пропонуємо вам два варіанти вирішення даного завдання.

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

Перший спосіб трансліта російського тексту досить сокирний - за допомогою програми Punto Switcher від Яндекс.

Викачуємо програму Punto Switcher на офіційному сайті: https://punto.yandex.ru/

Так от, ця програма, крім своєї основної функції можете перекладати текст в трансліт, змінювати регістр виділеного тексту (зробити з «иванов» в «ІВАНОВ») і інше.

Встановлюємо програму собі на комп`ютер, в системному треї повинен з`явиться значок програми

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

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

Далі ви просто копіюєте цей текст і вставляєте назад в Excel.

Це зручно якщо потрібно наприклад перевести в translit, наприклад один об`ємний стовпець даних російською мовою, але іноді перетворення тексту в трансліт потрібно за допомогою формул Excel, тому розглянемо другий спосіб з використанням користувальницької функції Excel, написаної на VBA

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

Є спеціальний макрос для створення користувальницької функції.

Function Translit (Txt As String) As StringDim Rus As VariantRus = Array ( "а", "б", "в", "г", "д", "е", "е", "ж", "з" , "і", "ї", "к", _ "л", "м", "н", "о", "п", "р", "с", "т", "у", "ф", "х", "ц", "ч", "ш", _ "щ", "ь", "и", "ь", "е", "ю", "я", " А "," Б "," В "," Г "," Д "," Е ", _" Е "," Ж "," З "," І "," Й "," К "," Л "," М "," Н "," О "," П "," Р ", _" С "," Т "," У "," Ф "," Х "," Ц "," Ч " , "Ш", "Щ", "Комерсант", "И", "Ь", "Е", "Ю", "Я") Dim Eng As VariantEng = 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 = 0For J = 0 To 65If Rus (J) = з Thenoutchr = Eng (J) flag = 1Exit ForEnd IfNext JIf flag Then outstr = outstr outchr Else outstr = outstr сNext ITranslit = outstrEnd 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 VariantDim i As IntegerDim J As IntegerDim c As StringDim flag As IntegerDim outchr As StringDim outstr As StringDim Rus As VariantRus = Array ( "а", "б", "в", "г", "д "," е "," е "," ж "," з "," і "," ї "," к ", _" л "," м "," н "," о "," п " , "р", "с", "т", "у", "ф", "х", "ц", "ч", "ш", _ "щ", "ь", "и", "ь", "е", "ю", "я", "А", "Б", "В", "Г", "Д", "Е", _ "Е", "Ж", " З "," І "," Й "," К "," Л "," М "," Н "," О "," П "," Р ", _" С "," Т "," У "," Ф "," Х "," Ц "," Ч "," Ш "," Щ "," Комерсант "," И "," Ь "," Е "," Ю "," Я ") Dim Eng As VariantEng = 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.Cellsoutstr = EmptyFor i = 1 To Len (Txt) з = Mid (Txt, i, 1) flag = 0For J = 0 To 65If Rus (J) = з Thenoutchr = Eng (J) flag = 1Exit ForEnd IfNext JIf flag Then outstr = outstr outchr Else outstr = outstr сNext iTxt.Value = outstrNext TxtEnd Sub

Отже, сподіваюся ви знайшли відповідь на своє питання про те, як найпростіше транслітерувати текст в Excel (перетворити текст на російській мові в трансліт).

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

SirExcel - безмежні можливості Excel