Как заменить русские буквы на английские в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Заменить русские буквы на английские в 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
Добрый день, уважаемые специалисты! Помогите пожалуйста. Вопрос вот в чем: есть два столбца, на разных листах, мне нужно их сравнить на предмет совпадений. Но беда в том, что в одной некоторые английские буквы заменены на русские (а именно те которые похожи по написанию). Использую Excel 2007. Подскажите пожалуйста, что с этим можно поделать? Спасибо
Вложения
| Лист Microsoft Office Excel.zip (7.7 Кб, 19 просмотров) |
Регистрация: 15.05.2008
Сообщений: 2,058
«Дайте людям рыбы, и вы накормите их на весь день; научите их ловить рыбу — и вы накормите их на всю жизнь»
«Большое спасибо» — Z261597841314, R208907249777, U447361470499
Программист VBA
СуперМодератор
Регистрация: 13.07.2008
Сообщений: 6,856
Я бы сначала макросом произвёл на обоих листах все необходимые замены,
(заменяем русскую А на латинскую А, и т.д. для всех букв алфавита)
а потом сравнил бы при помощи формул (типа =ПОИСКПОЗ или =СЧЁТЕСЛИ),
подсветив совпадающие значения условным форматированием.
__ Полезные надстройки для Excel. Парсинг сайтов и файлов.
Макросы любой сложности на заказ. Мониторинг цен конкурентов
| EducatedFool |
| Посмотреть профиль |
| Найти ещё сообщения от EducatedFool |
Участник клуба
Регистрация: 16.05.2010
Сообщений: 1,249
Вариант работает дальше, чем макрос, но быстрее, чем написание оного
Выделить данные, меню Правка-Найти/Заменить. За 3-4 минуты будет исправлено.
Последний раз редактировалось vikttur; 04.12.2010 в 07:49 .
Регистрация: 18.11.2010
Сообщений: 2
Спасибо большое за советы! Скажите пожалуйста, а как должен выглядеть макрос для замены русских букв на латинские?
Регистрация: 15.05.2008
Сообщений: 2,058
Вот так можно, для 2-х букв, дальше по аналогии (укажите свой диапазон)
Sub Replac() With [a1:f15] .Replace What:="c", Replacement:="с", LookAt:=xlPart .Replace What:="a", Replacement:="а", LookAt:=xlPart End With End Sub«Дайте людям рыбы, и вы накормите их на весь день; научите их ловить рыбу — и вы накормите их на всю жизнь»
«Большое спасибо» — Z261597841314, R208907249777, U447361470499
Как правильно настроить поиск и замену английских букв в русском тексте таблиц 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), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу.