Как заменить первую букву ячейки и сделать её заглавной

Процедура доступна в !SEMTools Lite и Pro.

Сделать первую букву ячейки заглавной в Excel можно формулой =ПРОПИСН(ЛЕВСИМВ(A1))&ПСТР(A1;2;ДЛСТР(A1)): ЛЕВСИМВ берёт первый символ, ПРОПИСН делает его заглавным, ПСТР возвращает остальной текст без изменений. Если остальные буквы нужно сделать строчными («ИВАНОВ» → «Иванов»), оберните вторую часть в СТРОЧН: =ПРОПИСН(ЛЕВСИМВ(A1))&СТРОЧН(ПСТР(A1;2;ДЛСТР(A1))). Функция ПРОПНАЧ сделает заглавной первую букву каждого слова, а начала всех предложений в выделенных ячейках исправит процедура !SEMTools в 1 клик.

Заменить первую букву ячейки на заглавную - одна из популярнейших задач в Excel. Почему? Дело в том, что часто текст при обработке теряет корректный регистр.

Допустим, вам пришлось добавить слова или текст к в начало нескольких ячеек. Если первая буква в них была заглавной - вам пришлось бы сделать весь текст ячейки маленькими буквами (например, функцией СТРОЧН), а после добавления нового слова — уже его делать с заглавной.

Или текст потерял пунктуацию и регистр после нормализации скриптом базы данных или другим человеком, после чего оригинал был удален и не сохранился.

А ваша задача — снова придать тексту читабельный вид. Для этого первые буквы каждой из ячеек нужно сделать заглавными.

Если объем текста небольшой, операцию можно проделать вручную, но при количестве ячеек в десятки, а тем более сотни и тысячи, задача быстро превращается в невыполнимую.

Так как же автоматизировать задачу и формулой сделать первую букву ячейки заглавной в Excel?

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

Первая буква заглавная — формула для первого символа ячейки

Как вы догадались из заголовка - допущение, на основе которого строится формула, звучит так:

Первый символ ячейки — всегда буква. А если нет — нас не тревожит регистр букв после него.

При таком допущении задача сводится к довольно простой - мы отсекаем первый символ от ячейки, переводим его в верхний регистр функцией ПРОПИСН и возвращаем в строку. Взять первый символ из ячейки можно разными способами, наиболее простой - функцией ЛЕВСИМВ:

=ЛЕВСИМВ(A1;1)

Оставшийся текст можно извлечь функцией ПСТР, а общая формула будет выглядеть так:

=ПРОПИСН(ЛЕВСИМВ(A1;1))&ПСТР(A1;2;ДЛСТР(A1))

Можно обойтись без извлечения оставшейся части слова, с помощью функции ЗАМЕНИТЬ:

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

Заменить первый символ ячейки на другой

Та же функция ЗАМЕНИТЬ меняет первый символ на любой другой текст: =ЗАМЕНИТЬ(A1;1;1;"Б") заменит первый символ на «Б», а =ЗАМЕНИТЬ(A1;1;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)))}

В Excel 365 и 2021 ту же формулу можно записать короче и без Ctrl+Shift+Enter: функция LET один раз вычисляет позицию первой буквы, а ПОСЛЕД заменяет громоздкое СТРОКА(ДВССЫЛ(…)):

=LET(п;ПОИСКПОЗ(ЛОЖЬ;СОВПАД(ПСТР(СТРОЧН(A1);ПОСЛЕД(ДЛСТР(A1));1);ПСТР(ПРОПИСН(A1);ПОСЛЕД(ДЛСТР(A1));1));0);ЗАМЕНИТЬ(A1;п;1;ПРОПИСН(ПСТР(A1;п;1))))

Сделать первые буквы выбранных ячеек заглавными в 1 клик

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

Чтобы не носить с собой текстовый документ с длиннющими формулами как та, что получилась выше, можно использовать надстройки для Excel, которые позволят решить задачу в 1 клик.

Такой является и моя надстройка !SEMTools.

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

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

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

• В начале ячейки пробел. Формула «поднимает» пробел, а буква остаётся строчной. Удалите лишние пробелы или используйте СЖПРОБЕЛЫ(A1) вместо A1.
• Первый символ — кавычка, скобка или цифра. Используйте формулу с поиском позиции первой буквы (LET или формулу массива выше).
• ПРОПНАЧ сделала заглавными все слова. Так и задумано: ПРОПНАЧ меняет первую букву каждого слова, а остальные делает строчными — «ООО» превратится в «Ооо». Для одной первой буквы используйте ПРОПИСН + ЛЕВСИМВ.
• Формула вернула #ЗНАЧ! или пустоту. Скорее всего, пропущена скобка (частая ошибка в ЗАМЕНИТЬ) или ячейка пустая. Проверьте баланс скобок.
• После удаления исходного столбца формулы показывают #ССЫЛ!. Сначала замените формулы значениями, потом удаляйте исходные данные.

Первая буква заглавная в Excel — частые вопросы

Как сделать первую букву заглавной в Excel?

Формулой =ПРОПИСН(ЛЕВСИМВ(A1))&ПСТР(A1;2;ДЛСТР(A1)). Чтобы остальные буквы стали строчными — =ПРОПИСН(ЛЕВСИМВ(A1))&СТРОЧН(ПСТР(A1;2;ДЛСТР(A1))).

Как сделать заглавной первую букву каждого слова?

Функцией =ПРОПНАЧ(A1): «иванов иван» → «Иванов Иван».

Как заменить первый символ в ячейке?

Функцией =ЗАМЕНИТЬ(A1;1;1;"новый") — третий аргумент 1 означает, что заменяется один символ.

Как сделать заглавными начала всех предложений в ячейке?

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

Итоги: как сделать первую букву заглавной

Надеюсь, на этой странице вы нашли то, что искали. Вас наверняка заинтересуют и похожие операции: изменить регистр в Excel и массовая замена значений.