Заменить русские буквы на английские в Excel
Часто бывает, что в тексте вместо английских букв (A, B, C, E, K, M, H, O, P, T) случайно введены буквы на русской раскладке. В результате слова, например, тable содержат ошибку. А если все буквы прописные (TABLE), то ошибку вовсе не заменить.
Такие ошибки накладывают ограничения на сортировку и поиск нужных данных. Для удаления этих ошибок служит функция =УБРАТЬСЛУЧКИРИЛ(ТЕКСТ), которая позволит заменить русские буквы на латинские.
Внимание! Не путайте ее с функцией транслитерации. Так как она не переводит б в b, п в p. Функция именно исправляет «Опечатки», т.е в переводится в b, т в t и так далее.
Функция имеет один аргумент
- ТЕКСТ — строка в которой необходимо заменить случайно введенные русские буквы на английские.
Ниже приведен пример работы данной формулы.
Код на VBA
Функция входит в состав надстройки VBA-Excel. Ниже привожу код для изучения.
Public Function УБРАТЬСЛУЧКИРИЛЛ(ТЕКСТ As String) As String Dim Rus As Variant Dim Eng As Variant Dim i As Double Dim j As Double Dim simb As String Dim FindLatin As Boolean Dim simbtrans As String Dim MergeText As String Eng = Array("a", "b", "c", "e", "k", "m", "n", "h", "o", "p", "t", "u", "y", "A", "B", "E", "K", "M", "O", "P", "C", "T", "H", "Y") Rus = Array("а", "в", "с", "е", "к", "м", "н", "н", "о", "р", "т", "и", "у", "А", "В", "Е", "К", "М", "О", "Р", "С", "Т", "Н", "У") For i = 1 To Len(ТЕКСТ) simb = Mid(ТЕКСТ, i, 1) FindLatin = False For j = 0 To 21 If Rus(j) = simb Then simbtrans = Eng(j) FindLatin = True Exit For End If Next If FindLatin Then MergeText = MergeText & simbtrans Else MergeText = MergeText & simb Next УБРАТЬСЛУЧКИРИЛЛ = MergeText End Function
Надстройка
VBA-Excel
Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.
Как заменить русские буквы на английские в excel
У меня база телефонов клиентов, очень большая, мне надо перевести имена , с русских букв на английские .
Пример: Наташа Разина = Natawa razina
Есть ли такая возможность .
Прикрепленные файлы
- пример.xlsx (31.64 КБ)
Пользователь
Сообщений: 2184 Регистрация: 25.02.2013
08.09.2017 13:51:49
Где это вы такую Ш видели. Теперь понятно почему в правах неправильно пишут.
Мастерство программиста не в том, чтобы писать программы, работающие без ошибок.
А в том, чтобы писать программы, работающие при любом количестве ошибок.
Пользователь
Сообщений: 14868 Регистрация: 21.12.2012
E-mail и реквизиты в профиле.
08.09.2017 14:05:12
| Цитата |
|---|
| Alemox написал: Где это вы такую Ш видели |
отож. А остальное:
Function ТРАНСЛИТ$(Текст$) ' транслитерация русских букв в английские (Alt+ScrollLock в Punto Switcher) Dim i% Dim RUS: RUS = _ Split("а б в г д е ё ж з и й к л м н о п р с т у ф х ц ч ш щ ъ ы ь э ю я А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я") Dim Eng: Eng = _ Split("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 = LBound(Eng) To UBound(Eng) Текст = Replace(Текст, Eng(i), RUS(i), 1) Next i ТРАНСЛИТ = Текст End Function
Я сам — дурнее всякого примера! .
Пользователь
Сообщений: 14868 Регистрация: 21.12.2012
E-mail и реквизиты в профиле.
08.09.2017 14:10:21
P.S. В названии «Добрый день !», «Целую», «Люби меня как я тебя и не забуду я тебя» лучше не писать:
| Цитата |
|---|
| 2.1. Название темы должно отражать смысл проблемы. |
Я сам — дурнее всякого примера! .
Пользователь
Сообщений: 37 Регистрация: 21.08.2017
08.09.2017 15:50:52
Причем тут люблю , целую , и тому подобное ! Я вижу люди на самом деле любят свинское отношение . И не надо не кого учить русскому , я более 30 лет не живу в России .
Если в состоянии помощь , помогите , если нет , то не надо умных высказывании .
Транслитерация в Excel, замена русских букв на английские
Технически транслитерация — это замена по списку значений — символов на определенные их эквиваленты в другой раскладке. Значит, и задача состоит в том, чтобы в Excel осуществить эту серию замен.
Удивительно, но сделать такую массовую замену и транслитерировать текст в Excel можно множеством способов. Обо всех подробнее далее.
Формула транслита с помощью стандартных функций Excel
Наименее изящное, тем не менее, самое простое решение задачи. В Excel для замены символов есть функция ПОДСТАВИТЬ (англ. SUBSTITUTE). Формула принимает на вход 4 аргумента:
- Текст для обработки, в котором будет производиться замена (текст);
- Заменяемый фрагмент (стар_текст);
- На что заменить (нов_текст);
- Какой по счету встреченный в значении фрагмент заменить. (номер_вхождения).
Четвертый параметр является опциональным и, если он не заполнен, заменяются все встреченные в тексте фрагменты.
Как раз то, что нам нужно! Поскольку Excel позволяет составлять сложные вложенные функции, мы можем сделать эту серию замен в одной много раз вложенной формуле.
Но есть несколько проблем, которые в сочетании не дают возможность сделать идеальную единую формулу транслита. Вот они:
- В русском алфавите 33 буквы.
- Идеальная формула транслита должна сохранять при замене регистр символов. Благо, функция «ПОДСТАВИТЬ» — регистрозависимая. Получается, что нужно делать не 33, а 66 замен. Но
- в Excel есть ограничение количества уровней вложенности в одной формуле — 64.
Будь в русском алфавите хотя бы на одну букву меньше, мы бы уложились в ограничения Excel. Есть ли решение? Да, довольно простое.
Формула на 64 уровня вложенности без заглавных Ъ и Ь
Как правило, заглавные буквы в словах бывают, если это начало слова. Или если это аббревиатура, но аббревиатуры тоже составляются из первых букв слов.
А вы знаете слова, начинающиеся с Ъ или Ь? 🙂
Кажется, их можно пропустить. Так мы экономим два уровня вложенности и укладываемся в лимиты Excel (66 — 2 = 64)!
Важно также учитывать, что при транслитерации заглавных букв Ж, Ш, Ч, Щ и т.д., когда английский эквивалент состоит из нескольких букв, заглавной на латинице должна быть только первая. Интернет полон решениями, где этот момент не учтен. Итак, вот наиболее полная корректная формула:
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ( ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ( ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ( ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ( ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ( ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ( ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ( ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ( A1; "А";"A");"Б";"B");"В";"V");"Г";"G");"Д";"D");"Е";"E");"Ё";"Yo");"Ж";"Zh");"З";"Z");"И";"I");"Й";"Y"); "К";"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");"ё";"yo");"ж";"zh");"з";"z");"и";"i");"й";"y");"к";"k"); "л";"l");"м";"m");"н";"n");"о";"o");"п";"p");"р";"r");"с";"s");"т";"t");"у";"u");"ф";"f");"х";"kh"); "ц";"ts");"ч";"ch");"ш";"sh");"щ";"sch");"ъ";"y");"ы";"y");"ь";"");"э";"e");"ю";"yu");"я";"ya") Копировать
Скачать файл-шаблон с транслитерацией
Формула выше на основе функции ПОДСТАВИТЬ с несколькими условиями может быть видоизменена — вместо явного прописывания текстовых значений, можно сделать ее на основе конкретных ячеек. Чем удобнее такой подход? Тем, что формулу менять не нужно, а данные для замены представлены в удобном для восприятия и редактирования формате в столбцах A и B.
По ссылке можно скачать файл-шаблон, в котором применены эти функции.
Формулы транслита, итоги
У подхода с созданием таких мега формул есть свои плюсы и минусы. Минус один — они громоздки и такое не так-то просто сходу набрать. Поэтому их нужно где-то хранить и копировать при необходимости.
Плюсы в основном происходят из недостатков VBA-кода:
- Формулы листа железно сработают в любой версии Excel на любом компьютере, даже с самыми жесткими политиками защиты данных.
- Они легко изменяются, если нужно переделать правила транслитерации, и довольно просты.
- Транспортабельны, т.е. формулы не «слетят», если их отправить кому-либо и открыть на другом компьютере.
Функция на VBA
Код пользовательской функции, которая транслитерирует строчные в строчные, а заглавные — в заглавные, и при этом транслитерирует заглавные буквы Ж, Ш, Х, Ч, Щ и подобные, делая в транслите заглавной только первую букву (Zh, Sh, Kh, Ch, Sch…).
Точь-в-точь эта функция присутствует в надстройке !SEMTools и доступна всем желающим — нужно только скачать и установить !SEMTools. Далее функцию можно применять как обычную функцию Excel на листе. Например, =Translit(A1)
Function Translit(x As String) As String cyr = "абвгдеёжзийклмнопрстуфхцчшщъыьэюя" lat = Array("", "a", "b", "v", "g", "d", "e", "e", "zh", "z", "i", "y", "k", _ "l", "m", "n", "o", "p", "r", "s", "t", "u", "f", "kh", "ts", "ch", _ "sh", "sch", "y", "y", "", "e", "yu", "ya") For i = 1 To 33 x = Replace(x, Mid(cyr, i, 1), lat(i), , , vbBinaryCompare) x = Replace(x, UCase(Mid(cyr, i, 1)), StrConv(lat(i), vbProperCase), , , vbBinaryCompare) Next Translit = x End Function Копировать
Заменить русские буквы на английские в Excel и наоборот
Надстройка !SEMTools предлагает помимо основного еще 4 варианта замены:
- Обратный транслит (из латиницы в кириллицу);
- «Смена раскладки» — asdf -> фыва;
- Замена букв с идентичным начертанием — кириллица в латиницу;
- Аналогичная замена английских букв, похожих на русские, на действительно русские.
Последние два пункта тесно связаны с ситуациями, когда удалось найти русские слова, содержащие латиницу или английские слова с кириллическими символами, и теперь нужно исправить такой текст.
Чтобы произвести транслитерацию, достаточно просто выделить столбец с данными и вызвать нужную процедуру. Данные изменятся на месте.
Нужно сделать транслитерацию русского на латиницу в Excel или наоборот?
Быстро решить эту и более 500 других задач поможет надстройка !SEMTools.
Как правильно настроить поиск и замену английских букв в русском тексте таблиц Excel
Одна из типовых ситуаций, с которой все мы однажды сталкиваемся, выглядит так: кто-то при наборе текстовой информации в ячейку случайно использовал английские буквы (латиницу) вместо русских (кириллицы). Допустить такую ошибку легко даже самим, особенно если учесть изощренное расположение некоторых символов (русская «С» и английская «С», например) в стандартной раскладке клавиатуры.
Использование символов латиницы в русском тексте порождает огромное количество проблем. От путаницы при банальной сортировке по алфавиту до некорректной консолидации данных при автоматическом объединении нескольких таблиц в одну. Выискивать похожие по виду символы и проверять, не являются ли они символами английской раскладки, крайне мучительно. Поэтому имеет смысл рассмотреть более изящные варианты решения этой задачи.
Способ 1. Шрифт без кириллицы
Выделите диапазон ячеек с проверяемым текстом и временно установите для него любой шрифт, не содержащий кириллицу, например Albertus, Bauhaus93 или любой аналогичный (находится методом перебора). Внешний вид символов кириллицы и латиницы станет отличаться, и можно будет легко визуально локализовать некорректные символы.
Шрифт без кириллицы
Способ 2. Подсветка латиницы красным цветом шрифта
Этот способ является продолжением и развитием предыдущей идеи и заключается в изменении цвета символов латиницы на красный с помощью небольшого макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert – Module) и введите туда следующий код.
1 2 3 4 5 6 7 8 9 10
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)) Or _ (Asc(Mid(c, i, 1)) >= 97 And Asc(Mid(c, i, 1)) Then c.Characters(Start:=i, Length:=1).Font.ColorIndex = 3 End If Next i Next c End Sub
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)) = 97 And Asc(Mid(c, i, 1))
Теперь если выделить интересующий диапазон ячеек и запустить наш макрос с помощью сочетания клавиш Alt+F8 или через вкладку Разработчик → Макросы (Developer → Macros), то символы латиницы выделятся красным цветом шрифта.
Символы латиницы выделятся красным цветом
Выделяйте только ячейки с текстом, а не весь столбец листа – это больше миллиона пустых ячеек, перебирать которые макрос будет долго.
Способ 3. Функция IsLatin на VBA
Если количество проверяемых ячеек велико, то визуальное определение станет затруднительным. Для таких случаев можно создать пользовательскую функцию (назовем ее, например, IsLatin), которая будет проверять, присутствуют ли в заданной ячейке символы английского алфавита, и выдавать в качестве результата логическое значение ИСТИНА или ЛОЖЬ.
Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert → Module) и введите туда текст этой небольшой функции.
1 2 3 4 5 6 7 8 9
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
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) и введите туда его текст.
1 2 3 4 5 6 7 8 9 10 11 12 13
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
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), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу.