Данные редко приходят в идеальном виде. Выгрузка из CRM, ответы из Google Формы, список, скопированный коллегой из письма, — всё это приносит лишние пробелы, разнобой в регистре и склеенные поля вроде «Иванов Иван» в одной ячейке. Пока данные «грязные», ничего не работает: условие =IF(A2="Москва"; ...) не срабатывает из-за невидимого пробела, а одинаковые по смыслу значения считаются разными.

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

Что уже умеем

В прошлом уроке вы научили таблицу принимать решения: описывали правила функцией IF, объединяли условия через AND и OR, строили проверки с несколькими вариантами. И там мы дважды столкнулись с неприятностью: сравнение текста не срабатывает, если в данных прячется лишний пробел или значение записано в другом регистре.

Сейчас закроем этот пробел. Разберём TRIM, LOWER, UPPER, PROPER, LEN, LEFT, RIGHT, MID, SPLIT и оператор &. Все они возвращают текст, поэтому их удобно вкладывать друг в друга и комбинировать с условиями из прошлого урока.

Что вы узнаете в рамках статьи

В этом уроке вы:

• очистите данные от лишних пробелов и приведёте их к единому регистру;
• измерите длину строки и поймёте, зачем это нужно;
• вырежете фрагмент функциями LEFT, RIGHT и MID;
• разделите склеенное значение функцией SPLIT;
• соберёте из нескольких столбцов готовую строку.

Ключевые термины

• Строка — любое текстовое значение в ячейке: имя, город, адрес почты, комментарий.
• TRIM — функция, убирающая пробелы по краям строки и лишние пробелы внутри.
• Регистр — написание букв заглавными или строчными; UPPER, LOWER и PROPER приводят его к нужному виду.
• LEN — функция, возвращающая количество символов в строке.
• Разделитель — символ, по которому строку делят на части: запятая, пробел, знак @.
• Оператор & — знак склейки: соединяет значения в одну строку.

Учебные данные: список участников из формы

Внесите данные ровно так, как показано, — с лишними пробелами и разнобоем в регистре. Это типичная выгрузка из формы регистрации:

Обратите внимание: в A3 между фамилией и именем два пробела, в B3 пробел в конце, а города написаны по-разному. Глазами всё читается, но для формул это разные значения.

Чистим данные: TRIM и регистр

Функция TRIM удаляет пробелы перед строкой и после неё, а внутри оставляет по одному между словами. Формула =TRIM(A2) превратит иванов иван в иванов иван, а ПЕТРОВА АННА — в ПЕТРОВА АННА. Изменение почти незаметно на вид, но именно оно заставит работать все дальнейшие сравнения.

Теперь регистр. Здесь три функции, и выбор зависит от того, что вам нужно:

Для имён подходит PROPER, для адресов почты — LOWER, а UPPER удобен, когда нужно свести значения к одному виду для сравнения.

Функции вкладываются друг в друга, и порядок важен: сначала чистим, потом меняем регистр. Введите в E2 итоговую формулу и скопируйте до E6:

=PROPER(TRIM(A2))

Получится аккуратный столбец:

Так же приведём в порядок города. В F2 введите =PROPER(TRIM(B2)) и скопируйте вниз: все варианты написания Москвы станут одинаковыми, и условие =IF(F2="Москва"; "Столица"; "Регион") из прошлого урока наконец сработает как задумано.

LEN: сколько в строке символов

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

Введите в свободную ячейку =LEN(A2) — получится 13, хотя в «иванов иван» 10 букв и один пробел между словами, то есть 11 символов. Разница в два символа и есть невидимые пробелы по краям. А =LEN(TRIM(A2)) даст 11: чистое значение.

Такую проверку удобно сочетать с IF. Формула =IF(LEN(C2)>20; "Длинный адрес"; "Норма") отметит слишком длинные адреса почты. Ещё LEN пригодится для контроля артикулов, телефонов и кодов фиксированной длины.

Режем строку: LEFT, RIGHT и MID

Иногда из строки нужна только часть. Здесь работают три функции:

Соберём короткий код города для отчёта. Введите в G2:

=UPPER(LEFT(TRIM(B2); 3))

Читайте изнутри наружу: TRIM убирает пробелы, LEFT берёт три первые буквы, UPPER делает их заглавными. Для строк 2–6 получится МОС, МОС, КАЗ, КАЗ, СОЧ.

SPLIT и склейка через &

Функция SPLIT разбивает строку по указанному разделителю и раскладывает части по соседним ячейкам. Разберём адрес почты на имя и домен. Введите в H2:

=SPLIT(LOWER(C2); "@")

В H2 появится ivanov, а в I2 — mail.ru. Скопируйте формулу до H6 и получите два готовых столбца. Обратите внимание на LOWER внутри: без него адрес A.Petrova@GMAIL.COM попал бы в таблицу в верхнем регистре и не совпал бы с остальными.

Важная деталь: SPLIT занимает столько ячеек, сколько получилось частей. Столбец I должен быть свободен, иначе Таблицы покажут ошибку.

Обратная задача — собрать строку из нескольких значений. Проще всего это делает оператор &. Введите в J2:

=E2 & " (" & F2 & ")"

Получится Иванов Иван (Москва) — готовая подпись для письма или отчёта. Вставки вроде пробела и скобок берутся в кавычки. То же умеет функция CONCATENATE, но запись через & короче.

[Скриншот: лист с «грязными» данными в столбцах A–C и очищенными результатами в E–J]

Частые ошибки

• Править данные вручную вместо формулы. Ручная чистка теряется при следующей выгрузке. Оставляйте исходный столбец нетронутым, а результат считайте формулой в соседнем — тогда новые данные обработаются сами.
• Неверный порядок вложенных функций. =LEFT(TRIM(B2); 3) и =TRIM(LEFT(B2; 3)) дадут разный результат: во втором случае вы отрежете три символа вместе с ведущим пробелом. Сначала чистите, потом режьте.
• Забыть, что формула зависит от исходного столбца. Если удалить столбец A, формулы в E покажут #REF!. Прежде чем убирать сырые данные, скопируйте результат и вставьте его через «Правка», затем «Специальная вставка», затем «Вставить только значения».
• Не оставить места для результата SPLIT. Функция раскладывает части по нескольким ячейкам, и если справа что-то есть, она вернёт ошибку. Освободите соседние столбцы заранее.

Частые вопросы

• TRIM не помог, лишний пробел остался. Почему?
  Скорее всего, это не обычный пробел, а невидимый символ из скопированного текста. Попробуйте =TRIM(CLEAN(A2)): функция CLEAN удаляет непечатаемые символы.
• Как разбить ФИО на отдельные столбцы?
  Подойдёт =SPLIT(TRIM(A2); " ") — фамилия и имя окажутся в разных ячейках. Убедитесь, что TRIM стоит внутри: иначе двойной пробел даст лишнюю пустую часть.
• Как считать по значениям после очистки?
  Так же, как по обычным: столбец с формулами ничем не отличается от столбца с данными. Например, =IF(F2="Москва"; 1; 0) по очищенным городам отработает без сюрпризов.

Итоги

Отличный результат: теперь у вас есть инструменты для самой неблагодарной части работы с данными. Вы умеете убирать лишние пробелы функцией TRIM, приводить написание к единому виду через PROPER, UPPER и LOWER, измерять длину строки функцией LEN, вырезать фрагменты с помощью LEFT, RIGHT и MID, разбивать значения функцией SPLIT и склеивать их обратно оператором &. А главное — вы увидели, что функции вкладываются друг в друга, и одна формула может выполнить всю цепочку преобразований сразу.

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