Изменить регистр в Excel

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

Многие системы статистики (например, Wordstat Яндекса) выводят данные в нормализованном виде. Для исправления их написания необходимы особые функции управления регистром.

Функции изменения регистра Excel

В Excel из коробки доступны 3 функции для изменения регистра: СТРОЧН, ПРОПИСН, ПРОПНАЧ. Первая делает все буквы маленькими, вторая — большими.

С третьей (ПРОПНАЧ) все чуть более необычно. Она делает заглавным каждый первый символ, следующий за символом, не являющимся буквой. В связи с этим некоторые слова будут преобразовываться некорректно: кое-какой -> Кое-Какой, волей-неволей -> Волей-Неволей и т.п. Когда объём данных небольшой, такого рода погрешности легко проверить и исправить вручную. Если же данных много, заниматься редакторской деятельностью вряд ли есть время.

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

Надстройка !SEMTools содержит все самые востребованные функции, связанные с изменением регистра букв.

Как заменить заглавные буквы на строчные в Excel

Функцией СТРОЧН

Чтобы заменить заглавные буквы на строчные, в Excel есть функция СТРОЧН (подробнее в статье по ссылке). Как и любые функции, она требует ручной ввод в отдельную ячейку.

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

Макросом в 1 клик

Можно ли изменить данные на месте? Да, здесь поможет VBA-процедура в !SEMTools.

В отличие от функции, она позволяет произвести изменения, не создавая отдельный столбец. Достаточно выделить необходимые данные и вызвать процедуру в меню «Изменить — Символы».

Сделать все буквы заглавными

Когда нужно сделать все маленькие буквы большими, есть также 2 варианта — функция и процедура.

Функцией ПРОПИСН

Функция ПРОПИСН делает все строчные буквы заглавными, а остальные символы не меняет. Также требует создания доп. столбца.

Примеры на картинке ниже:

Макросом в 1 клик

Если нужно изменить данные быстро и на месте без формул, можно воспользоваться процедурой надстройки !SEMTools для Excel.

Каждое слово с заглавной

В отличие от аналогичной базовой функции ПРОПНАЧ в самом Excel, этот макрос считает разделителем слов только пробел.

Первая буква ячейки с заглавной

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

Приводится множество решений, у каждого из которых — свои недостатки.

Формула — вариант 1

Наиболее примитивная формула берет первый символ ячейки, применяет к нему функцию ПРОПИСН и заменяет этот символ результатом, не трогая остальной текст ячейки:

=ЗАМЕНИТЬ(A1;1;1;ПРОПИСН(ЛЕВСИМВ(A1;1)))

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

Формула — вариант 2

Чуть более продвинутая формула позволит извлечь первое слово из ячейки, применить к нему функцию ПРОПНАЧ и заменить исходное слово результатом:

=ЕСЛИОШИБКА(ПРОПНАЧ(ЛЕВСИМВ(A1;ПОИСК(" ";A1)-1)) & ЗАМЕНИТЬ(A1;1;ПОИСК(" ";A1);" ");ПРОПНАЧ(A1))

Один побочный эффект функции ПРОПНАЧ — если исходное слово было написано полностью заглавными буквами, все буквы, кроме первой в нем станут строчными. Это может испортить вид аббревиатур и других подобных слов.

Но и это еще не все. Ведь первое слово извлекается путем поиска в строке первого пробела. А если перед ним не слово, а например, тире? Или это число? Итоговая задача — сделать заглавной именно первую букву — не будет решена.

Формула — вариант 3

Как мы поняли из формулы выше, ни первое слово, ни тем более первый символ строки не могут в 100% случаев содержать буквы.

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

При работе с русскоязычными текстами будет достаточно считать буквами символы кириллицы и латиницы.

В таком случае формулы будут похожи на те, что в статье про поиск латиницы и кириллицы в ячейках. За одним исключением — в них используется МИН, а не СЧЁТ.

Поскольку СЧЁТ пропускает ошибки, а МИН нет, также задействована функция ЕСЛИОШИБКА, которая для всех ошибок вернет заведомо большое число (в данном случае 1000).

Итак,
сделать заглавным первый кириллический символ:

{=ПОДСТАВИТЬ(A1;ПСТР(A1;МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(192:223));A1);1000));1);ПРОПИСН(ПСТР(A1;МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(192:223));A1);1000));1));1)}

Сделать заглавным первый символ латиницы:

{=ПОДСТАВИТЬ(A1;ПСТР(A1;МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1);1000));1);ПРОПИСН(ПСТР(A1;МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1);1000));1));1)}

Формула — вариант 4

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

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

Процедура !SEMTools

Все перечисленные выше решения на основе сложных формул не решают основную пользовательскую задачу — сделать заглавными первые буквы предложений.

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

Иными словами, позволяет сэкономить кучу времени.

Одним кликом переводим первые буквы предложений из строчных в заглавные:

Латиница с заглавной

Надстройка !SEMTools умеет различать слова по содержащимся в них символам, в числе которых латиница. Данный макрос позволяет сделать такие слова с большой буквы в кейсах, когда это нужно (например, иностранные бренды).

Слова с латиницей заглавными

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

Исправление регистра топонимов

Данная функция надстройки уникальна, иными словами, никакого похожего решения вы больше не найдете.

Функция меняет первые буквы слов и фраз-топонимов (географических наименований) со строчных на заглавные. Важно, что она не просто делает первую букву заглавной, но и понимает такие топонимы, как «СПб».

Распознать аббревиатуры

Еще одна уникальная функция надстройки. Макрос определяет аббревиатуры как на кириллице, так и на латинице, и преобразовывает их написание в верхний регистр.