Excel изменить регистр букв после первой буквы
При работе с текстовым контентом зачастую необходима нормализация текста. В ее рамках все буквы приводятся к нижнему или верхнему регистру для последующей статистической обработки.
Многие системы статистики (например, Wordstat Яндекса) выводят данные в нормализованном виде. Для исправления их написания необходимы особые функции управления регистром.
Функции изменения регистра Excel
В Excel из коробки доступны 3 функции для изменения регистра: СТРОЧН, ПРОПИСН, ПРОПНАЧ. Первая делает все буквы маленькими, вторая — большими.
Любые другие насущные задачи, связанные с изменением регистра букв (например, начинать предложения с заглавной буквы) придется решать при помощи настройки пользовательских функций или макросов.
Надстройка !SEMTools содержит все самые востребованные функции, связанные с изменением регистра букв.
Как заменить заглавные буквы на строчные в Excel
Функцией СТРОЧН
Чтобы заменить заглавные буквы на строчные, в Excel есть функция СТРОЧН (подробнее в статье по ссылке). Как и любые функции, она требует ручной ввод в отдельную ячейку.
Далее, если исходные данные больше не понадобятся, нужно будет удалить все формулы из ячеек, в которых применена эта функция, и только после этого удалять столбец с заглавными буквами.
Макросом в 1 клик
Можно ли изменить данные на месте? Да, здесь поможет VBA-процедура в !SEMTools.
В отличие от функции, она позволяет произвести изменения, не создавая отдельный столбец. Достаточно выделить необходимые данные и вызвать процедуру в меню «Изменить — Символы».
Изменение регистра — заменить заглавные букв на строчные
Сделать все буквы заглавными
Когда нужно сделать все маленькие буквы большими, есть также 2 варианта — функция и процедура.
Функцией ПРОПИСН
Функция ПРОПИСН делает все строчные буквы заглавными, а остальные символы не меняет. Также требует создания доп. столбца.
Примеры на картинке ниже:
Макросом в 1 клик
Если нужно изменить данные быстро и на месте без формул, можно воспользоваться процедурой надстройки !SEMTools для Excel.
Изменение регистра — заменить строчные буквы на заглавные
Каждое слово с заглавной
В отличие от аналогичной базовой функции ПРОПНАЧ в самом Excel, этот макрос считает разделителем слов только пробел.
Первая буква ячейки с заглавной
Один из очень популярных вопросов по Excel — как сделать первую букву ячейки заглавной, не трогая остальные буквы.
Приводится множество решений, у каждого из которых — свои недостатки.
Формула — вариант 1
Наиболее примитивная формула берет первый символ ячейки, применяет к нему функцию ПРОПИСН и заменяет этот символ результатом, не трогая остальной текст ячейки:
Проблема этого решения в том, что первый символ не обязательно будет буквой! Ячейка может начинаться со скобок, кавычек, многоточия и других символов.
Формула — вариант 2
Чуть более продвинутая формула позволит извлечь первое слово из ячейки, применить к нему функцию ПРОПНАЧ и заменить исходное слово результатом:
Один побочный эффект функции ПРОПНАЧ — если исходное слово было написано полностью заглавными буквами, все буквы, кроме первой в нем станут строчными. Это может испортить вид аббревиатур и других подобных слов.
Но и это еще не все. Ведь первое слово извлекается путем поиска в строке первого пробела. А если перед ним не слово, а например, тире? Или это число? Итоговая задача — сделать заглавной именно первую букву — не будет решена.
Формула — вариант 3
Как мы поняли из формулы выше, ни первое слово, ни тем более первый символ строки не могут в 100% случаев содержать буквы.
Поэтому наша задача сводится к другой — найти позицию первой буквы в ячейке, и уже потом изменить её регистр.
При работе с русскоязычными текстами будет достаточно считать буквами символы кириллицы и латиницы.
В таком случае формулы будут похожи на те, что в статье про поиск латиницы и кириллицы в ячейках. За одним исключением — в них используется МИН, а не СЧЁТ.
Поскольку СЧЁТ пропускает ошибки, а МИН нет, также задействована функция ЕСЛИОШИБКА, которая для всех ошибок вернет заведомо большое число (в данном случае 1000).
Итак,
сделать заглавным первый кириллический символ:
Сделать заглавным первый символ латиницы:
Формула — вариант 4
Можно ли найти любую первую букву в ячейке, вне зависимости от языка? Ведь помимо основных символов, есть еще символы других алфавитов, диакритические символы и т.д.
Это из области фантастики, но она есть. И задействует множество необычных функций Excel. Подробнее о ней можно прочитать в статье про формулы массива.
Процедура !SEMTools
Все перечисленные выше решения на основе сложных формул не решают основную пользовательскую задачу — сделать заглавными первые буквы предложений.
Поэтому и была создана соответствующая процедура в надстройке. Она позволяет избежать громоздких формул массива и прочих сложнейших комбинаций функций, создания дополнительных столбцов и удаления их после получения нужного результата.
Иными словами, позволяет сэкономить кучу времени.
Одним кликом переводим первые буквы предложений из строчных в заглавные:
Латиница с заглавной
Надстройка !SEMTools умеет различать слова по содержащимся в них символам, в числе которых латиница. Данный макрос позволяет сделать такие слова с большой буквы в кейсах, когда это нужно (например, иностранные бренды).
Слова с латиницей заглавными
Этот макрос преобразовывает все буквы слов на латинице в заглавные. Например, как на картинке ниже, — когда нужно выявить названия моделей технических изделий и перевести их в верхний регистр.
Исправление регистра топонимов
Данная функция надстройки уникальна, иными словами, никакого похожего решения вы больше не найдете.
Функция меняет первые буквы слов и фраз-топонимов (географических наименований) со строчных на заглавные. Важно, что она не просто делает первую букву заглавной, но и понимает такие топонимы, как «СПб».
Распознать аббревиатуры
Еще одна уникальная функция надстройки. Макрос определяет аббревиатуры как на кириллице, так и на латинице, и преобразовывает их написание в верхний регистр.
Наверняка многие из вас сталкивались с задачей изменения текста с той целью, чтобы его было удобнее воспринимать и читать, например, перевести заглавные буквы в строчные (проще говоря, большие в маленькие) или наоборот, строчные буквы в заглавные (маленькие в большие), представить текст в стандартном виде (как в предложениях), поменять регистр на противоположный и т.д.
Поэтому, несмотря на то, что Microsoft Excel в основном ориентирован на работу с числовыми данными, все-таки не стоит забывать и про возможность работы с текстовыми данными.
Вместо того, чтобы перенабирать все записи вручную или если просто лень этим заниматься, то предлагаю рассмотреть более удобные способы изменения регистра букв в Excel.
Хотя Excel в полной мере и не обладает возможностями Microsoft Word, тем не менее варианты перевода символов в верхний и нижний регистры есть, давайте подробнее остановимся на 3 основных способах:
Применение Microsoft Word
Метод требует ручного ввода текстовых данных, поэтому для большого объема данных крайне неудобный, в связи с чем такой способ имеет смысл применять только для разовых задач и если нет необходимости автоматизировать процесс.
Стандартные функции Excel
Excel предлагает нам 3 стандартные функции для преобразования данных в строчные (все строчные) и прописные буквы (ВСЕ ПРОПИСНЫЕ), а также смешанную расстановку (Начинать С Прописных).
Детально изучим каждый из вариантов конвертации текстовых данных.
Как сделать все буквы строчными в Excel?
Сделать перевод всех символов в строчные можно с помощью функции СТРОЧН (английская версия LOWER) в Excel:
СТРОЧН(текст)
Делает все буквы в строке текста строчными.
Применим функцию СТРОЧН, где в качестве аргумента выбираем произвольный текст, в котором мы хотим сконвертировать все символы в строчные:
Как сделать все буквы заглавными (прописными) в Excel?
Для перевода текстовых данных в большие буквы можно обратиться к функции ПРОПИСН (английская версия UPPER):
ПРОПИСН(текст)
Делает все буквы в строке текста прописными.
Принцип действия аналогичный, применяем функцию ПРОПИСН, аргументом указываем текст, который мы планируем сделать состоящим только из заглавных символов:
Как сделать первую букву заглавной в Excel?
Для изменения начального символа каждого слова в тексте из строчного в заглавный можно воспользоваться функцией ПРОПНАЧ (английская версия PROPER):
ПРОПНАЧ(текст)
Начинает текстовую строку с заглавной буквы; делает прописной первую букву в каждом слове текста, преобразуя все другие в строчные.
Все то же самое, что и в предыдущих примерах, применяем к данным функцию ПРОПНАЧ, в котором хотим конвертировать начальные буквы слов в большие, а все оставшиеся сделать маленькими:
Однако при работе с ПРОПНАЧ необходимо учитывать, что Excel воспринимает знаки препинания как начало нового слова.
В данном случае не только запятая, точка, пробел, вопросительный или восклицательный знак означают конец слова, но и любые другие знаки препинания (двоеточие, точка с запятой, кавычки, скобки, дефис, тире и т.д.).
Из-за этого могут возникнуть ситуации, когда некоторые слова преобразуются правильно (пример алма-ата -> Алма-Ата), а некоторые некорректно (пример кое-что -> Кое-Что).
Применение пользовательских функций (UDF)
Переходим в редактор Visual Basic (горячая клавиша Alt + F11), добавляем новый модуль и записываем код новой функции (напротив основных действий кода даются пояснения):
В Microsoft Excel есть три замечательных функции, при помощи которых Вы можете изменять регистр текста. Это UPPER (ПРОПИСН), LOWER (СТРОЧН) и PROPER (ПРОПНАЧ).
- Функция UPPER (ПРОПИСН) преобразовывает все символы нижнего регистра в верхний регистр.
- Функция LOWER (СТРОЧН) делает все прописные буквы строчными.
- Функция PROPER (ПРОПНАЧ) делает в каждом слове первую букву прописной, а все остальные – строчными.
Все эти три функции работают по одинаковому принципу, поэтому я покажу Вам, как работает одна из них. Давайте в качестве примера рассмотрим функцию UPPER (ПРОПИСН):
Вводим формулу в Excel
- Вставьте новый (вспомогательный) столбец рядом с тем, в котором содержится текст, который Вы хотите преобразовать.
Замечание: Этот шаг не обязателен. Если таблица не большая, Вы можете просто использовать любой смежный пустой столбец.
- Введите знак равенства (=) и имя функции UPPER (ПРОПИСН) в смежную ячейку нового столбца (B3).
- В скобках после имени функции введите соответствующую ссылку на ячейку (C3). Ваша формула должна выглядеть вот так:
где C3 – это ячейка с текстом, который нужно преобразовать.
Копируем формулу вниз по столбцу
Теперь Вам нужно скопировать формулу в остальные ячейки вспомогательного столбца:
- Выделите ячейку с формулой.
- Наведите указатель мыши на маленький квадрат (маркер автозаполнения) в нижнем правом углу выделенной ячейки, чтобы указатель превратился в маленький черный крест.
- Нажмите и, удерживая левую кнопку мыши, протяните формулу вниз по всем ячейкам, в которые нужно её скопировать.
- Отпустите кнопку мыши.
Замечание: Если Вам нужно полностью заполнить новый столбец (на всю высоту таблицы), то Вы можете пропустить шаги 5-7 и просто дважды кликнуть по маркеру автозаполнения.
Функция ПРОПИСН
- Возможно, после нажатия на функциональную кнопку автоматически отобразится функция СУММ. В этом случае замените функцию СУММ на ПРОПИСН. [1]
- Так вы замените функцию текстовыми данными, что позволит удалить первый столбец с текстом.
Функция ПРОПНАЧ
- Если в строке формул автоматически отобразится функция СУММ, замените ее на ПРОПНАЧ.
- Ячейки с функциями скопируются в виде текстовых данных, что позволит удалить первый столбец.
Мгновенное заполнение (Excel 2013)
- Если справа от столбца с именами нет пустого столбца, щелкните правой кнопкой мыши по букве столбца с именами и в меню выберите «Вставить». Справа отобразится новый пустой столбец.
- Перед удалением убедитесь, что функция мгновенного заполнения правильно преобразовала все имена.
Заменить заглавные на строчные
Позволяет перевести все заглавные буквы, содержащиеся в словах, в строчные. В отличие от базовой функции, содержащийся в самом Excel, позволяет произвести изменения непосредственно в столбце с данными, не создавая отдельный столбец.
Сделать все буквы заглавными
Переводит все буквы их строчных в заглавные. Создание отдельного столбца также не требуется.
Каждое слово с заглавной
В отличие от аналогичной базовой функции ПРОПНАЧ в самом Excel, этот макрос считает разделителем слов только пробел.
Начала предложений с заглавной
Одним кликом переводим первые буквы предложений из строчных в заглавные.
Как сделать верхний регистр для символов текста в Excel
Пример 1. В таблице содержатся числовые данные в римской форме записи. При этом для использован нижний регистр символов. Преобразовать символы в верхний регистр.
Вид таблицы данных:
Используем формулу, чтобы заполнить сразу все ячейки:
- A2:A7 – диапазон ячеек с римскими числами в нижнем регистре.
Если требуется получить универсальную формулу, которая определяет, содержат ли ячейки текстовые представления римских чисел, можно добавить проверочное условие:
С помощью функции ЕСЛИ проверим возможность преобразования римского числа к арабскому. Если ЕОШ вернет значение ИСТИНА (есть ошибка), будет выведено соответствующее пояснение, иначе функция ПРОПИСН выполнит преобразование регистра символов.
Добавим несколько значений в таблицу и выведем результаты в соседнем столбце:
Как указать день недели большой буквой в Excel?
Пример 2. В таблице Excel содержится столбец с датами. В соседнем столбце вывести первый символ названия дня недели, соответствующий каждой дате. Символ должен быть в верхнем регистре.
Вид таблицы данных:
Выделим ячейки B2:B7 и запишем следующую формулу массива:
Для преобразования дат в трехсимвольный стандарт сокращения названий дней недели можно воспользоваться другой формулой:
Надстройки для Excel
Самое простое это, конечно, установить надстройку для Excel, например ASAP Utilities. Здоровым плюсом является наличие бесплатной лицензии для установки дома.
Среди многих функций есть нужные нам для работы с текстом
Excel — первая буква Заглавная
Данный вопрос имеет два варианта решения
- Только первая буква в тексте будет заглавная
- Большая буква каждого нового слова
Как видите ситуацию бывают разные поэтому нам надо рассмотреть все варианты решения данных вопросов поэтому будет разбирать по максимум!
Только «Первая» буква заглавная
Данный пример как раз использую достаточно часто как описывал выше при настройки Яндекс Директа, когда собираешь с вордстата большое количество ключей и делаешь для каждого слова свое объявление и тут как раз возникает необходимость сделать все красиво, а точнее что бы начало предложение начиналось с большой буквы.
Для этого нам поможет формула:
- заменить — это функция синтаксис который выглядит так: ЗАМЕНИТЬ(стар_текст;начальная_позиция;число_знаков;нов_текст)Стар_текст — Текст, в котором требуется сделать заменуНачальная_позиция — Координата символа в Стар_текст с которого начнется заменаЧисло_знаков — Число символов от Начальная_позиция которые нужно заменитьНов_текст — Текст который заменяем
- D4 — старый текст
- 1 — начальная позиция (первая буква)
- 1 — число знаков отчета (первая буква)
- ПРОПИСН — функция которая делает букву прописной
Вот такой не сложной формулой, можно как ниже в примере сделать в предложении заглавную букву, что придаст объявлению нормальный вид!
Что такое верхний и нижний регистр на клавиатуре
Эти понятия дошли до нас со времён печатных машинок. При наборе текстов, обычный шрифт написания выполняли в стандартном положении оборудования, которое называли нижним, а заглавные буквы наносились на бумагу при изменении положения печатных штанг, путём перевода их в верхнее положение. Именно подобной функцией наделены клавиатуры современных мобильных и стационарных печатных устройств.
С помощью кнопки функционального перевода «Shift» из одного режима в другой, пользователь ПК может переходить на прописные или заглавные буквы при написании текстов, в зависимости от требований к содержанию. Цифры при этом обозначают знаки препинания или другую символику, которую используют при распечатке документов. Иными словами, верхний регистр – это такой режим печати, в котором буквы становятся заглавными, а цифры меняются на символы. Нижний – это обычное изображение цифровых и буквенных обозначений.
В этой статье я хочу рассказать Вам о нескольких способах как изменить регистр символов в Excel с верхнего на нижний или как сделать каждое слово с прописной буквы. Вы научитесь справляться с такими задачами при помощи функций ПРОПИСН и СТРОЧН, при помощи макросов VBA, а также используя Microsoft Word.
Проблема в том, что в Excel не предусмотрен специальный инструмент для изменения регистра текста на рабочем листе. Остаётся загадкой, почему корпорация Microsoft снабдила Word такой мощной функцией и не добавила её в Excel. Это упростило бы решение многих задач для большинства пользователей. Но не торопитесь лихорадочно перенабирать вручную все текстовые данные Вашей таблицы! К счастью, есть несколько хороших способов преобразовать текстовые значения в ячейках в верхний или нижний регистр, или же сделать каждое слово с прописной буквы. Позвольте мне поделиться с Вами этими способами.
Функции Excel для изменения регистра текста
В Microsoft Excel есть три замечательных функции, при помощи которых Вы можете изменять регистр текста. Это UPPER (ПРОПИСН), LOWER (СТРОЧН) и PROPER (ПРОПНАЧ).
- Функция UPPER (ПРОПИСН) преобразовывает все символы нижнего регистра в верхний регистр.
- Функция LOWER (СТРОЧН) делает все прописные буквы строчными.
- Функция PROPER (ПРОПНАЧ) делает в каждом слове первую букву прописной, а все остальные – строчными.
Все эти три функции работают по одинаковому принципу, поэтому я покажу Вам, как работает одна из них. Давайте в качестве примера рассмотрим функцию UPPER (ПРОПИСН):
Вводим формулу в Excel
- Вставьте новый (вспомогательный) столбец рядом с тем, в котором содержится текст, который Вы хотите преобразовать.
Замечание: Этот шаг не обязателен. Если таблица не большая, Вы можете просто использовать любой смежный пустой столбец.
- Введите знак равенства (=) и имя функции UPPER (ПРОПИСН) в смежную ячейку нового столбца (B3).
- В скобках после имени функции введите соответствующую ссылку на ячейку (C3). Ваша формула должна выглядеть вот так:
где C3 – это ячейка с текстом, который нужно преобразовать.
Копируем формулу вниз по столбцу
Теперь Вам нужно скопировать формулу в остальные ячейки вспомогательного столбца:
- Выделите ячейку с формулой.
- Наведите указатель мыши на маленький квадрат (маркер автозаполнения) в нижнем правом углу выделенной ячейки, чтобы указатель превратился в маленький черный крест.
- Нажмите и, удерживая левую кнопку мыши, протяните формулу вниз по всем ячейкам, в которые нужно её скопировать.
- Отпустите кнопку мыши.
Замечание: Если Вам нужно полностью заполнить новый столбец (на всю высоту таблицы), то Вы можете пропустить шаги 5-7 и просто дважды кликнуть по маркеру автозаполнения.
Удаляем вспомогательный столбец
Итак, у Вас есть два столбца с одинаковыми текстовыми данными, отличающимися только регистром. Предполагаю, что Вы хотите оставить столбец только с нужным вариантом. Давайте скопируем значения из вспомогательного столбца и избавимся от него.
- Выделите ячейки, содержащие формулу, и нажмите Ctrl+C, чтобы скопировать их.
- Кликните правой кнопкой мыши по первой ячейке исходного столбца.
- В контекстном меню в разделе Paste Options (Параметры вставки) выберите Values (Значения).Поскольку нам нужны только текстовые значения, мы выберем именно этот вариант, чтобы в будущем избежать ошибок в формулах.
- Кликните правой кнопкой мыши по любой ячейке вспомогательного столбца и в контекстном меню выберите команду Delete (Удалить).
- В диалоговом окне Delete (Удаление ячеек) выберите вариант Entire column (Столбец) и нажмите ОК.
В теории это может показаться слишком сложным. Расслабьтесь и попробуйте проделать все эти шаги самостоятельно. Вы увидите, что изменение регистра при помощи функций Excel – это совсем не трудно.
Изменяем регистр текста в Excel при помощи Microsoft Word
Если Вы не хотите возиться с формулами в Excel, Вы можете изменить регистр в Word. Далее Вы узнаете, как работает этот метод:
- Выделите диапазон на листе Excel, в котором необходимо изменить регистр текста.
- Нажмите Ctrl+C или кликните правой кнопкой мыши и в контекстном меню выберите команду Copy (Копировать).
- Создайте новый документ Word.
- Нажмите Ctrl+V или щелкните правой кнопкой мыши по пустой странице и в контекстном меню выберите команду Paste (Вставить). Таблица Excel будет скопирована в Word.
- Выделите текст, у которого нужно изменить регистр.
- На вкладке Home (Главная) в разделе Font (Шрифт) нажмите иконку Change Case (Регистр).
- В раскрывающемся списке выберите один из 5 вариантов регистра.
Замечание: Кроме этого, Вы можете нажимать сочетание Shift+F3, пока не будет установлен нужный стиль. При помощи этих клавиш можно выбрать только верхний и нижний регистр, а также регистр как в предложениях.
Теперь у Вас есть таблица в Word с изменённым регистром текста. Просто скопируйте её и вставьте на прежнее место в Excel.
Изменяем регистр текста при помощи макроса VBA
Вы также можете использовать макросы VBA в Excel 2010 и 2013. Не переживайте, если Ваши знания VBA оставляют желать лучшего. Какое-то время назад я тоже мало что знал об этом, а теперь могу поделиться тремя простыми макросами, которые изменяют регистр текста на верхний, нижний или делают каждое слово с прописной буквы.
Я не буду отвлекаться от темы и рассказывать Вам, как вставить и запустить код VBA в Excel, поскольку об этом замечательно рассказано в других статьях нашего сайта. Я просто покажу макросы, которые Вы можете скопировать и вставить в свою книгу.
- Если Вы хотите преобразовать текст в верхний регистр, используйте следующий макрос VBA:
- Чтобы применить нижний регистр к своим данным, используйте код, показанный ниже:
- Вот такой макрос сделает все слова в тексте, начинающимися с большой буквы:
Я надеюсь, что теперь, когда Вы знаете пару отличных приёмов изменения регистра в Excel, эта задача не вызовет у Вас затруднений. Функции Excel, Microsoft Word, макросы VBA – всегда к Вашим услугам. Вам осталось сделать совсем немного – определиться, какой из этих инструментов Вам больше нравится.
Читайте также: