Как изменить регистр текста в Excel, Google Таблицах и Word
В Excel и Google Таблицах регистр текста меняют тремя функциями: =UPPER(A2), =LOWER(A2) и =PROPER(A2). Их вводят во вспомогательный столбец, а затем вставляют обратно как значения. В Microsoft Word выделите текст и нажмите Shift+F3, чтобы по кругу переключать «все строчные», «ВСЕ ПРОПИСНЫЕ» и «Начинать с прописных», или воспользуйтесь кнопкой «Регистр» на вкладке «Главная»: там есть ещё «Как в предложениях.» и «иЗМЕНИТЬ РЕГИСТР».
Это закрывает большинство задач. Остальная часть руководства — о случаях, когда что-то идёт не так: начнём с PROPER, которая странно обходится с апострофами, и перейдём к более удобным способам на тот случай, когда одну и ту же чистку нужно делать каждую неделю.
UPPER, LOWER и PROPER в Excel и Google Таблицах
В обеих программах три функции работают одинаково. Каждая берёт фрагмент текста и возвращает преобразованную копию; исходную ячейку ни одна из них не меняет. Формулы и команды меню здесь приведены по английской версии программ: в русскоязычном Excel эти функции называются ПРОПИСН (UPPER), СТРОЧН (LOWER) и ПРОПНАЧ (PROPER), а аргументы разделяются точкой с запятой. Обычный порядок действий:
- Вставьте пустой столбец рядом с данными.
- В первой строке введите
=PROPER(A2)(илиUPPER, илиLOWER) и протяните формулу вниз. - Скопируйте новый столбец и вставьте его поверх исходного через «Специальная вставка» (Paste Special) → «Значения» (Values): текст сохранится, а формулы исчезнут.
- Удалите вспомогательный столбец.
В Excel для Microsoft 365 протягивать формулу не обязательно: =PROPER(A2:A500) сама разольёт результаты вниз по столбцу. В Google Таблицах оберните диапазон в ARRAYFORMULA: =ARRAYFORMULA(PROPER(A2:A)).
Ни в одной из программ нет функции для стиля «Регистр предложения» (Sentence case). Эта формула делает заглавной первую букву ячейки, а все остальные буквы строчными:
=UPPER(LEFT(A2,1)) & LOWER(MID(A2,2,LEN(A2)))Она считает всю ячейку одним предложением, что подходит для названий товаров и коротких подписей. В абзацах она делает строчной первую букву каждого предложения, кроме первого.
Странности PROPER: O'Neil, Don'T и 3Rd
Собственное описание Microsoft объясняет всё, что делает PROPER: она «capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter» (делает заглавной первую букву текстовой строки и любые другие буквы, которые идут после любого символа, не являющегося буквой), а остальные буквы делает строчными (см. страницу Microsoft о функции PROPER). Апострофы, цифры и дефисы — это всё «characters other than a letter» («символы, не являющиеся буквами»), поэтому получается так:
| Исходный текст | Результат PROPER | Почему |
|---|---|---|
o'neil | O'Neil | Буква после апострофа; здесь, как ни странно, получилось верно |
don't stop | Don'T Stop | То же правило, но на этот раз неверно |
3rd street | 3Rd Street | Буква после цифры (пример Microsoft: 76BudGet превращается в 76Budget) |
2-way street | 2-Way Street | Буква после дефиса |
mcdonald | Mcdonald | Особых правил для Mc и Mac нет |
IBM SALES | Ibm Sales | Всё после первой буквы переводится в строчные |
lord of the rings | Lord Of The Rings | Каждое слово, в том числе короткие |
У PROPER в Google Таблицах такая же привычка с апострофами: «don't» она превращает в «Don'T», а в справке Google предупреждают, что «mcLeod» превращается в «Mcleod».
Как исправить проблему с апострофами
Чаще всего в английском страдают притяжательные формы и сокращения, оканчивающиеся на 's и 't. Эта формула исправляет их, не трогая O'Neil и O'Sullivan:
=TRIM(SUBSTITUTE(SUBSTITUTE(PROPER(A2&" "), "'S ", "'s "), "'T ", "'t "))Хитрость в лишнем пробеле, добавленном в конец текста. Благодаря ему "'S " находит притяжательную форму и в самом конце ячейки, а TRIM потом убирает пробел. Фраза «don't stop ada's car» превращается в «Don't Stop Ada's Car». Если в ваших данных есть 're, 'll, 've, 'd и 'm, добавьте такие же пары и для них, а также повторите все пары с изогнутым апострофом (’), если текст пришёл из Word или из интернета.
Никакая формула не исправит McDonald или IBM, потому что нужной информации в тексте нет. Для столбца с именами примените PROPER, затем отсортируйте или отфильтруйте данные по «Mc», «Mac» и известным аббревиатурам и поправьте их вручную.
Очистите текст, прежде чем менять регистр
Преобразование регистра лучше работает на чистых данных. Функция Excel TRIM убирает пробелы по краям и сводит последовательности пробелов между словами к одному, но, как отмечает Microsoft, она обрабатывает только обычный пробел, а не неразрывный, который часто попадается в тексте, скопированном с веб-страниц. Сначала замените такие пробелы:
=PROPER(TRIM(SUBSTITUTE(A2, UNICHAR(160), " ")))Наше руководство по нормализации текстовых данных идёт дальше: там разобраны невидимые символы, диакритика и удаление дублей.
Flash Fill: преобразование по образцу
В настольной версии Excel (2013 и новее) Flash Fill («Мгновенное заполнение») может понять, как менять регистр, по вашему образцу. Рядом с ALICE SMITH введите Alice Smith, перейдите в ячейку ниже и нажмите Ctrl+E (или выберите «Данные» → «Мгновенное заполнение»). Excel заполнит остальную часть столбца по тому же образцу.
Нужно знать две вещи. Flash Fill записывает обычные значения, а не формулы, поэтому результаты не обновятся, если изменится источник. И он угадывает закономерность по вашим примерам, поэтому просмотрите результаты, особенно строки, которые не похожи на первую. Если он угадал неверно, введите ещё один пример и снова нажмите Ctrl+E.
Power Query для чисток, которые вы повторяете
Если один и тот же экспорт приходит вам на почту каждую неделю, делайте преобразование в Power Query, чтобы оно повторялось при каждом обновлении. Выделите данные, выберите «Данные» → From Table/Range, затем в редакторе Power Query выделите столбец и выберите Transform → Format и один из вариантов: lowercase, UPPERCASE или Capitalize Each Word. Те же команды в разделе Add Column → Format помещают результат в новый столбец и сохраняют оригинал.
За меню стоят функции языка M: Text.Lower, Text.Upper и Text.Proper. В документации Microsoft показано, как применять их через Table.TransformColumns:
= Table.TransformColumns(Source, {"CUSTOMER", Text.Proper})Две особенности Power Query сбивают с толку пользователей Excel. Его команда Format → Trim убирает пробелы только по краям текста; в отличие от функции листа TRIM, двойные пробелы в середине она не трогает. А язык M чувствителен к регистру, поэтому «Redmond» и «REDMOND» при слиянии или группировке считаются разными значениями, если не привести их к одному регистру заранее или не сравнивать через Comparer.OrdinalIgnoreCase.
Microsoft Word: Shift+F3 и кнопка «Регистр»
Word меняет сами буквы, вспомогательный столбец не нужен. Выделите текст и нажмите Shift+F3 (на многих клавиатурах Mac — Fn+Shift+F3), чтобы по кругу переключать «все строчные» (lowercase), «ВСЕ ПРОПИСНЫЕ» (UPPERCASE) и «Начинать с прописных» (Capitalize Each Word). Кнопка «Регистр» (Change Case) на вкладке «Главная» (Home), на которой изображено «Aa», предлагает пять вариантов: «Как в предложениях.» (Sentence case.), «все строчные», «ВСЕ ПРОПИСНЫЕ», «Начинать с прописных» и «иЗМЕНИТЬ РЕГИСТР» (tOGGLE cASE). Последний выручает, когда вы набрали абзац с включённым Caps Lock.
Не путайте это с Ctrl+Shift+A: он применяет эффект шрифта «Все прописные» (All Caps). Текст с таким форматированием выглядит набранным заглавными буквами, но сами буквы не меняются, поэтому при вставке куда-нибудь как обычный текст он возвращается к исходному регистру. Если заглавные буквы должны уцелеть при переносе в веб-форму или куда-либо ещё, где принимается обычный текст, используйте «Регистр». Ctrl+Shift+K — аналогичный эффект для малых прописных (small caps).
В Google Документах та же возможность есть в меню Format → Text → Capitalization с вариантами lowercase, UPPERCASE и Title Case.
Какой способ выбрать
- Разовый столбец: вспомогательный столбец с PROPER, UPPER или LOWER, вставленный обратно как значения. Flash Fill — если проще показать образец, чем набирать формулу.
- Один и тот же импорт каждую неделю: Power Query.
- Связный текст в документе: «Регистр» в Word или Shift+F3.
- Заголовки, которым нужен настоящий «Регистр заголовка» (Title Case): PROPER делает заглавными «of» и «the». Вставьте столбец в наш конвертер регистра: его «Регистр заголовка» оставляет короткие слова строчными и работает построчно, так что строки остаются строками. (Имена он тоже не знает: «o'neil» превращается в «O'neil».) Не уверены, какой стиль нужен вашим заголовкам? Мы сравнили их в руководстве по «Регистру заголовка» и «Регистру предложения».
