Как заменить все русские буквы на английские в excel

Транслит

Если Вам часто приходится переводить кириллицу в транслит (а попробуйте-ка сделать это для фамилии «Кржижановский», например), то эта функция — для Вас.

Откройте редактор Visual Basic (Alt+F11), вставьте через меню новый пустой программный модуль (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", "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

Теперь на любом листе этой книги Вы можете использовать эту функцию, вставив ее через Мастер Функций:

• в Excel 2003 и старше — через меню Вставка — Функция (Insert — Function)

• в Excel 2007 и новее — через вкладку Вставка — Функция (Insert — Function)

 из категории Определенные пользователем (User defined):

В этом макросе варианты замены русских букв английскими эквивалентами представлены согласно ГОСТ. Если для вашей задачи нужны другие версии (например, русская «я» должна выводиться как «ja», а не как «ya» и т.д.), то можно свободно подкорректировать это в теле макроса. Как легко догадаться, два первых массива Rus и Eng как раз и кодируют эти подстановки.

Для новых версий Excel 2007-2010 и т.д. не забудьте сохранить вашу книгу как файл с поддержкой макросов (Macro Enabled Workbook), т.е. в формате XLSM.

Ссылки по теме

• Макрос-переводчик
• Преобразование русского текста в транслит с помощью надстройки PLEX

Онлайн транслитерация ФИО для загранпаспорта, авиабилета, ЧПУ для Яндекса и просто для удобного чтения

Транслитерация (лат. trans – ‘через’ + litera – ‘буква’) – точная передача букв письменности одного языка алфавитом другого языка.

Транслитерация с русского на английский: задачи, преимущества

Транслитерация кириллической азбуки буквами латинского алфавита обеспечивает стабильность представления исходного письменного текста в англоязычной среде или при отсутствии клавиатуры с буквами кириллицы. Задача транслитерации – унификация (единообразие), формальное сближение русского текста с языком международного общения.

Транслитерацию применяют при оформлении документов межгосударственного значения (паспорт, банковские карты, водительские удостоверения и пр.).

Она используется в компьютерных программах для доменных имён сайтов, veb-адресов; ввода текстов при ограничениях применения кириллицы. Названия папок и файлов на латинице исключают сбой при работе с рядом программ. Транслитерированная форма текста устраняет проблему их сохранения/открытия в разных кодировках.

Её применяют в нерусифицированных системах для набора текстов. SMS-сообщения на латинице более компактны, чем кириллические.

Транслитерация с русского на английский: особенности замены букв

Транслитерация – не механическая побуквенная замена. Она ориентируется на способы передачи звучащей речи средствами чуждой письменности. В ней применяют и буквы, и их сочетания, и специальные значки.

Трудности транслитерации с русского на английский определяются тем, что составы алфавитов совпадают частично: английский алфавит состоит из 26 букв, русский – из 33.

ГОСТ 27465-87 установил для транслитерации базовый и расширенный латинский алфавит.

Базовый – буквы, которые используются в большинстве письменных систем и с которыми соотносятся буквы кириллицы: а-a, б-b, в-v, г-g, д-d, з-z, и-i, к-k, л-l, м-m, н-n, о-o, п-p, р-r, с-s, т-t, у-u, ф-f, э-e.

Виды транслитерации разработаны ГОСТами, регламентирующими перевод кириллического письма на латиницу.

— ослабленная. Одна буква заменяется буквосочетаниями (ч → ch, я → ya);

— расширенная. Позволяет обозначение звуков/букв исходного языка особыми знаками, не входящими в алфавит, или представление определённых русских буквосочетаний одной буквой (ый → y);

— буквосочетания заменяют буквами, которые по отдельности обозначают другие звуки (-ья → ja).

ГОСТ-2000 определяет две системы преобразования русской азбуки латинскими буквами:

— ослабленную, с применением буквосочетаний. Она задаёт представление русских букв сочетаниями букв латинских (система Б).

Обе системы разработаны в виде таблиц (см. таблицы 1, 2).

Транслитерация русского алфавита по системе А

Транслитерация русского алфавита по системе Б

Принцип и правила транслитерации с русского на английский

Таблицы замены букв – это техническая основа перевода текста из одной письменной системы в другую и одновременно правила транслитерации.

Передачу русских текстов латиницей регулирует принцип звуковых соответствий букв двух алфавитов. На нём основаны правила транслитерации:

— однозначность. Каждая буква текста-исходика заменяется одним знаком. Как правило, это буквы, обозначающие тождественные звуки;

— простота. Соотносятся буквы, которые в разных языках обозначают тождественные звуки. Они позволяют опираться на несложные табличные алгоритмы;

— обратимость. Возможность конвертировать преобразованный текст в исходный.

Несмотря на то, что две системы транслитерации составлены с максимальным учётом фонетических и графических соответствий, правила конверсии допускают звуковые несоответствия знаков двух систем, но сводят их к минимуму. Грамотная транслитерация обеспечивает восстановление конвертированного текста носителями языка.

Обзор старых стандартов и видов транслитерации

Правила транслитерации русских текстов английским алфавитом разработаны несколькими ГОСТами, регулируются приказами, правилами, инструкциями государственных учреждений (МВД, ФМС и др.).

ГОСТ-71 – первый стандарт для транслитерации

Основой регламентирования транслитерации стал ГОСТ-71, изложивший требования к записи в научно-технических текстах славянских наименований латиницей. Согласно его положениям, бОльшая часть букв русской азбуки при графическом преобразовании соотносится с латинскими буквами, совпадающими с ними значениями и начертательными признаками.

Для обозначения русских букв и звуков, которых нет в английском языке, в ГОСТе-71 представлены две системы соответствий кириллических и латинских букв. Обе действуют во всех последующих ГОСТах:

— система А, в которой буквы Ё, Я Ж, Э, Щ, Ю, Ч, Ш передаются буквами латиницы с диакритическими знаками (такие значки уточняют произношение);

— система Б. В ней указанные буквы соотносятся с латинскими сочетаниями из 2-3 букв.

Транслитерация кириллической азбуки буквами латинского алфавита обеспечивает стабильность представления исходного письменного текста в англоязычной среде или при отсутствии клавиатуры с буквами кириллицы. Задача транслитерации – унификация (единообразие), формальное сближение русского текста с языком международного общения.

Транслитерация для авиабилетов, паспортов, транслитерация для ЧПУ страниц яндекса и гугла

Дело в том, что будь вы китайцем или японцем, то для выражения своих мыслей вам понадобилось бы использовать как минимум несколько тысяч иероглифов, и клавиатура с таким функционалом стала бы фейком, хотя можно было бы попробовать научиться печатать и на таком монстре (нужен только подходящий клавиатурный тренажер):

Если вам необходимо выполнить транслит русских букв в английские.

Сохраняем файл 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(НомерЯчейки)

Содержимое скрыть 1 Формула транслитерации с использованием стандартных функций Excel 1.1 Формула для 64 уровней вложенности без прописных букв b и b 1.2 Скачать файл шаблона с транслитерацией 1.3 Формулы транслитерации, итоги 2 Функция VBA 3 Заменить русские буквы английскими буквами в Excel и наоборот

Как перевести кириллицу в транслитерацию в Excel?

Технически транслитерация — это замена списка значений — символов на их конкретные эквиваленты в другом макете. Это означает, что задача состоит в том, чтобы реализовать этот набор переопределений в Excel.

Удивительно, но есть много способов сделать такую ​​массовую замену и транслитерировать текст в Excel. Подробнее обо всех ниже.

Наименее элегантное, но самое простое решение проблемы. В Excel есть функция REPLACE для замены символов. Формула принимает 4 аргумента в качестве входных данных:

1. Текст для обработки, в котором будет произведена замена (текст)
2. Фрагмент для замены (old_text)
3. На что заменить (новый_текст)
4. Какой фрагмент встречается по смыслу, подлежащему замене. (число_звук)

Четвертый параметр является необязательным, и если он не заполнен, заменяются все фрагменты, встречающиеся в тексте.

Как раз то, что нам нужно! Поскольку Excel позволяет составлять сложные вложенные функции, мы можем выполнять эту серию замен во вложенной формуле много раз.

Но есть несколько проблем, которые в совокупности не позволяют создать идеальную унифицированную формулу транслитерации. Они здесь:

• В русском алфавите 33 буквы
• Идеальная формула транслитерации должна сохранять заглавные буквы во время замены. К счастью, функция REPLACE чувствительна к регистру. Получается, нужно сделать не 33, а 66 замен. Но
• в Excel есть ограничение на количество уровней вложенности в формуле: 64.

Если бы в русском алфавите было хотя бы на одну букву меньше, мы были бы в рамках Excel. Есть выход? Да, довольно просто.

Формула на 64 уровня вложенности без заглавных Ъ и Ь

Как правило, в словах есть заглавные буквы, если это начало слова. Или, если это аббревиатура, но аббревиатуры также состоят из первых букв слов.

Вы знаете слова, которые начинаются на боб? 🙂

Похоже, их пропустили. Так что давайте сохраним два уровня вложенности и настроимся на пределы Excel (66 — 2 = 64)!

также важно учитывать, что при транслитерации заглавных букв Ж, Ш, Ч, Щ и т.д., когда английский эквивалент состоит из нескольких букв, только первая должна быть заглавной латинскими буквами. В Интернете полно решений, где этот момент не учитывается. Итак, вот наиболее полная правильная формула:

= ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ) (ПОДСТАВИТЬ (ПОДСТАВИТЬ)) (ПОДСТАВИТЬ (ПОДСТАВИТЬ) (ПОДСТАВИТЬ (ПОДСТАВИТЬ) (ЗАМЕНА (A1; «)»; « В »;« В »);« Г »;« Г »);« Д »;« Д »);« Е »;« Д »);« Е »;« Йо »);« Ж »;« Ж » «);» З «;» Z «);» И «;» I «);» Y «;» Y «);» К «;» K «);» Л «;» L «);» М »;« M »);« N »;« N »);« O »;« O »);« P »;« P »);« P »;« R »);« C »;« S »); «Т»; «Т»); «У»; «U»); «Ф»; «F»); «Х»; «Х»); «Ц»; «Ц»); «Ч»; «Ч»); «Ш»; «Ш»); «Щ»; «Щ»); «Y»; «Y»); «А ТАКЖЕ»; «А ТАКЖЕ»); «U»; «Ю»); «Я ЕСМЬ»; «Я»); «а»; «а»); «б»; «б»); «с»; «v»); «грамм»; «грамм»); «д»; «г»); «А также»; «А также»); «А также»; «йо»); «е»; «ж»); «з»; «з»); «е»; «я»); «у»; «у»); «к»; «к»); «L»; «L»); «м»; «м»); «п»; «п»); «или»; «или»); «п»; «п»); «п»; «r»); «c»; «s»); «t»; «t»); «y»; «u»); «f»; «f»); «x»; «kh»); «ц»; «ц»); «час»; «ч»); «ш»; «ш»); «u»; «щ»); «ъ»; «у»); «с»; «y»); «b»; «»); «e»; «e»); «yu»; «yu»); «i»; «ya»)

Скачать файл-шаблон с транслитерацией

Вышеупомянутая формула, основанная на функции REPLACE с различными условиями, может быть изменена: вместо явного написания текстовых значений вы можете основывать ее на определенных ячейках. Почему такой подход дешевле? Тот факт, что формулу менять не нужно, а данные для замены представлены в удобном для чтения и редактирования формате в столбцах A и B.

Транслитерация с таблицей подстановок и формулой с 64 уровнями вложенности

По ссылке можно загрузить файл шаблона, в котором применяются эти функции.

Формулы транслита, итоги

Подход к созданию таких мега-формул имеет свои плюсы и минусы. Есть только один недостаток — они громоздкие, и их не так-то просто набрать сразу. Поэтому их нужно где-то хранить и при необходимости копировать.
Плюсы в основном связаны с недостатками кода VBA:

• По иронии судьбы формулы электронных таблиц будут работать в любой версии Excel на любом компьютере, даже с самыми строгими политиками защиты данных
• Их легко редактировать, если вам нужно переделать правила транслитерации, и они довольно просты
• Переносной, т.е формулы не «улетают», если их отправить кому-то и открыть на другом компьютере

Функция на VBA

Код пользовательской функции, которая транслитерирует строчные буквы в строчные и прописные в прописные и одновременно транслитерирует прописные буквы Ж, Ш, Х, Ч, Щ и т.п., используя только первую букву транслитерации (Zh, Ш, Х, Ч, Щ…).

Правильно, эта функция присутствует в надстройке SEMTools и доступна всем: вам просто нужно скачать и установить! Затем эту функцию можно применить на листе как обычную функцию Excel. Например, = Транслит (A1)

Функция транслита (x как строка) как строка cyr = «abvgdeozhziyklmnoprstufkhtschshshchshch’yueyuya» 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 «) Для i = от 1 до 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 варианта замены в дополнение к основному:

1. Обратная транслитерация (с латиницы на кириллицу)
2. «Изменить макет» — asdf -> fiva
3. Замена букв в едином стиле — с кириллицы на латиницу
4. Аналогичная замена английских букв, похожих на русские, на истинно русские

Последние два пункта тесно связаны с ситуациями, когда можно было найти русские слова, содержащие латинский алфавит, или английские слова с кириллическими символами, и теперь необходимо исправить этот текст.

Одна из типовых ситуаций, с которой все мы однажды сталкиваемся, выглядит так: кто-то при наборе текстовой информации в ячейку случайно использовал английские буквы (латиницу) вместо русских (кириллицы). Допустить такую ошибку легко даже самим, особенно если учесть изощренное расположение некоторых символов (русская «С» и английская «С», например) в стандартной раскладке клавиатуры.

Использование символов латиницы в русском тексте порождает огромное количество проблем. От путаницы при банальной сортировке по алфавиту до некорректной консолидации данных при автоматическом объединении нескольких таблиц в одну. Выискивать похожие по виду символы и проверять, не являются ли они символами английской раскладки, крайне мучительно. Поэтому имеет смысл рассмотреть более изящные варианты решения этой задачи.

Способ 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)) <= 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) и введите туда текст этой небольшой функции.

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

Как можно заметить, макрофункция использует интересный оператор 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

Теперь если выделить на листе диапазон и запустить наш макрос сочетанием клавиш Alt+F8 или на вкладке Разработчик → Макросы (Developer → Macros), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу.