Заменить первую букву ячейки на заглавную – одна из популярнейших задач в Excel. Почему? Дело в том, что часто текст при обработке теряет корректный регистр.
Допустим, вам пришлось добавить слова или текст к в начало нескольких ячеек. Если первая буква в них была заглавной – вам пришлось бы сделать весь текст ячейки маленькими буквами (например, функцией СТРОЧН), а после добавления нового слова — уже его делать с заглавной.
Или текст потерял пунктуацию и регистр после нормализации скриптом базы данных или другим человеком, после чего оригинал был удален и не сохранился.
А ваша задача — снова придать тексту читабельный вид. Для этого первые буквы каждой из ячеек нужно сделать заглавными.
Если объем текста небольшой, операцию можно проделать вручную, но при количестве ячеек в десятки, а тем более сотни и тысячи, задача быстро превращается в невыполнимую.
Так как же автоматизировать задачу и формулой сделать первую букву ячейки заглавной в Excel?
В зависимости от количества допущений, задача может быть решена несколькими способами, рассмотрим их от простого к сложному.
Сделать заглавным первый символ ячейки
Как вы догадались из заголовка – допущение, на основе которого строится формула, звучит так:
Первый символ ячейки — всегда буква. А если нет — нас не тревожит регистр букв после него.
При таком допущении задача сводится к довольно простой – мы отсекаем первый символ от ячейки, переводим его в верхний регистр функцией ПРОПИСН и возвращаем в строку. Взять первый символ из ячейки можно разными способами, наиболее простой – функцией ЛЕВСИМВ:
=ЛЕВСИМВ(A1;1)
Оставшийся текст можно извлечь функцией ПСТР, а общая формула будет выглядеть так:
=ПРОПИСН(ЛЕВСИМВ(A1;1))&ПСТР(A1;2;ДЛСТР(A1))
Можно обойтись без извлечения оставшейся части слова, с помощью функции ЗАМЕНИТЬ:
=ЗАМЕНИТЬ(A1;1;1;ПРОПИСН(ЛЕВСИМВ(A1;1))
Сделать заглавной первую букву первого слова
А разве это не то же самое, что и вариант выше? Спросите вы, и отчасти будете правы, но иногда первый символ ячейки не является буквой.
Это могут быть скобки, кавычки в различных их вариантах, многоточие, возможно, другие знаки препинания.
В таких случаях формулы выше не сработают – они возьмут этот знак, попытка изменить его регистр ничего не изменит, и вернут его обратно.
Тогда мы сделаем более сложное допущение:
Первая буква в строке всегда находится в первом слове.
Формально знаки препинания не входят в состав первого слова. Но когда стоит задача взять только первое слово из строки, первым словом считаются все символы до первого пробела.
Именно путем поиска этого пробела и решается поставленная задача.
А вот полная формула. Подробное описание принципа ее работы здесь: функция ПРОПНАЧ.
=ЕСЛИОШИБКА(ПРОПНАЧ(ЛЕВСИМВ(A1;ПОИСК(" ";A1)-1)) & ЗАМЕНИТЬ(A1;1;ПОИСК(" ";A1);" ");ПРОПНАЧ(A1))
Прелесть этой функции в том, что она игнорирует любые знаки препинания (а также цифры и прочие не буквенные символы) и действительно делает первую букву слов заглавной. В этой формуле мы всего лишь говорим ей сделать это только с одним, первым, словом.
Найти позицию первой буквы в ячейке
Как уже было сказано в описании формул выше, они не идеальны, т.к. полагаются на определенные допущения.
Если же мы действительно хотим сделать заглавной именно первую букву ячейки, иначе говоря, первый символ ячейки, являющийся буквой, мы должны сначала вычислить его позицию.
Позиция первого символа кириллицы или латиницы
Если снова пуститься в допущения и считать буквами только кириллицу или латиницу, то помогут формулы массива с функцией ПОИСК.
Формулы очень похожи на те, что используются, когда нужно найти латиницу в русском тексте (формулы в статье) или наоборот.
Исключение в том, что используется не СЧЁТ, а МИН, т.к. задача не посчитать числа и сравнить результат с нулем, а найти минимальное число, которое и будет позицией символа.
Формула для поиска позиции первого символа латиницы:
{=МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1);""))}
Похожая считает буквой только кириллицу:
{=МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(192:223));A1);"");ЕСЛИОШИБКА(ПОИСК("ё";A1);""))}
Обратите внимание, позиция символа “ё” ищется отдельно, т.к. он не входит в сплошной диапазон символов. Подробнее об этом можно почитать в описании функции СИМВОЛ.
Ну и общая формула, считающая буквой и кириллицу, и латиницу:
{=МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1);"");ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(192:223));A1);"");ЕСЛИОШИБКА(ПОИСК("ё";A1);""))}
Как вы наверняка заметили, во всех формулах присутствует функция ЕСЛИОШИБКА — она нужна для того, чтобы функция МИН при обработке массивов не вернула ошибку.
Позиция первой буквы на любом языке
Как вы наверняка заметили, диапазоны символов выше не исчерпывают все буквы всех алфавитов, которые могут менять регистр. И поэтому такие допущения будут давать сбои на текстах на других языках.
Есть ли формула, которая позволит найти первую букву строки и сделать ее заглавной, если она в принципе может быть строчной и быть переведена в заглавный регистр?
Ответ – да, такая формула есть! И в ней используется именно эта особенность букв. Они могут менять регистр, в отличие от любых остальных символов — цифр, знаков препинания и других символов.
Их отличие в том, что при смене регистра они становятся фактически другим символом таблицы UNICODE, и проверить это легко функцией СОВПАД или самой функцией UNICODE.
Итак, формулы:
Первая использует для сравнения СОВПАД:
{=ПОИСКПОЗ(ЛОЖЬ;СОВПАД(ПСТР(СТРОЧН(A1);СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1);ПСТР(ПРОПИСН(A1);СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1));0)}
Вторая – UNICODE:
{=МИН(ЕСЛИ(UNICODE(ПРОПИСН(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)))<>UNICODE(СТРОЧН(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)));СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));""))}
Это формулы массива, они вводятся без фигурных скобок, сочетанием клавиш Ctrl + Shift + Enter — тогда фигурные скобки появятся в формуле сами.
Формулы могут показаться сложными и громоздкими, и это так, но, кажется, это лучшее, что может сегодня предложить Excel для бескомпромиссного поиска позиции первой буквы.
Сделать заглавной любую первую букву
Так мы плавно подобрались к формуле, которая исполнит нашу заветную цель. По-настоящему сделает заглавной первую букву ячейки A1.
Фактически она является вот этой формулой на основе функции ЗАМЕНИТЬ и ПСТР.
Функция ЗАМЕНИТЬ заменяет текст на определенной позиции, ПСТР извлекает его на основе неё же, а ПРОПИСН — делает его заглавным:
=ЗАМЕНИТЬ(A1;ПОЗИЦИЯ;1;ПРОПИСН(ПСТР(A1;ПОЗИЦИЯ;1)))
Но вместо слова ПОЗИЦИЯ используется формула из предыдущего раздела для поиска той самой позиции.
Вот итоговая формула:
{=ЗАМЕНИТЬ(A1;ПОИСКПОЗ(ЛОЖЬ;СОВПАД(ПСТР(СТРОЧН(A1);СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1);ПСТР(ПРОПИСН(A1);СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1));0);1;ПРОПИСН(ПСТР(A1;ПОИСКПОЗ(ЛОЖЬ;СОВПАД(ПСТР(СТРОЧН(A1);СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1);ПСТР(ПРОПИСН(A1);СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1));0);1)))}
Сделать первые буквы выбранных ячеек заглавными в 1 клик
Формула выше решает задачу. Но если в ячейке несколько предложений и нужно сделать с заглавной каждое из них — решений нет вовсе.
Чтобы не носить с собой текстовый документ с длиннющими формулами как та, что получилась выше, можно использовать надстройки для Excel, которые позволят решить задачу в 1 клик.
Такой является и моя надстройка !SEMTools.
Она позволяет избежать громоздких формул массива и прочих сложнейших комбинаций функций, создания дополнительных столбцов и удаления их после получения нужного результата.
Иными словами, позволяет экономить время и нервы при работе с текстом. В ней более 500 различных процедур, одна из которых — перевод первых букв предложений в заглавные:
Смотрите также:
- Формулы массива;
- Изменить регистр в Excel;
- Массовая замена значений в Excel;
- Заменить русские буквы на английские.
Нужно изменить регистр букв в Excel?
В !SEMTools есть это и сотни других готовых решений!