Текстовые функции Excel: как разделить, склеить и очистить данные

Какая функция решает какую задачу

У «грязного» текста в таблице обычно одна из трёх проблем: лишние символы, несколько значений в одной ячейке или одно значение, разбросанное по нескольким ячейкам. Для каждой есть свой инструмент:

Порядок важен: сначала очистка, потом разделение или склейка. Иначе лишний пробел превращает «Азиз» и «Азиз » в два разных значения, и ВПР или сводные таблицы перестают их сопоставлять.

Ниже формулы записаны с английскими именами и запятой. В русской версии Excel используются русские названия функций и точка с запятой: =ЛЕВСИМВ(A2;5).

Очистка: TRIM, CLEAN и SUBSTITUTE

• TRIM убирает пробелы в начале и конце строки и сжимает повторяющиеся пробелы между словами до одного.
• CLEAN удаляет непечатаемые символы: переносы строк и служебные коды, которые часто приходят с выгрузками из других систем.
• SUBSTITUTE заменяет один фрагмент другим — везде или только N-е вхождение.

Частая ловушка: в тексте, скопированном с сайтов, встречается неразрывный пробел (символ 160). TRIM его не видит, поэтому сначала замените его обычным:

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

Разделение ФИО

Допустим, в столбце A записано «Каримов Азиз Бахтиёрович».

TEXTSPLIT (Microsoft 365 и Excel в браузере) делит строку по разделителю и выводит части в соседние ячейки:

=TEXTSPLIT(TRIM(A2), " ")

Получаются три столбца: фамилия, имя, отчество. Ячейки справа должны быть пустыми, иначе появится ошибка #ПЕРЕНОС! (#SPILL!).

В старых версиях используйте LEFT, MID и RIGHT вместе с FIND, которая возвращает позицию символа:

Фамилия:  =LEFT(A2, FIND(" ", A2) - 1)
Имя:      =MID(A2, FIND(" ", A2) + 1, FIND(" ", A2, FIND(" ", A2) + 1) - FIND(" ", A2) - 1)
Отчество: =RIGHT(A2, LEN(A2) - FIND(" ", A2, FIND(" ", A2) + 1))

Эти формулы ломаются, если в ФИО только два слова, поэтому оборачивайте их в IFERROR (ЕСЛИОШИБКА) или заранее проверяйте количество пробелов.

LEFT, MID и RIGHT хороши и для кодов фиксированного формата: из SKU-0153-RED формула =MID(A2, 5, 4) вернёт 0153.

Приведение телефонов к одному формату

Телефоны в клиентской базе обычно записаны по-разному: 90 123-45-67, (90) 1234567, +998901234567. Чтобы привести их к единому виду, уберите всё, кроме цифр, возьмите последние 9 цифр и добавьте код страны:

=LET(
  d, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, " ", ""), "-", ""), "(", ""), ")", ""), "+", ""),
  "+998" & RIGHT(d, 9)
)

LET доступна в Microsoft 365. В старых версиях вынесите цепочку SUBSTITUTE во вспомогательный столбец. Затем проверьте длину: =LEN(B2)=13 быстро покажет номера, в которых при вводе потеряли цифру.

Склейка: TEXTJOIN и &

TEXTJOIN объединяет диапазон через разделитель и умеет пропускать пустые ячейки:

=TEXTJOIN(" ", TRUE, B2:D2)

Второй аргумент TRUE игнорирует пустые значения, поэтому отсутствующее отчество не оставит двойной пробел. Для двух-трёх значений хватит оператора &: =B2 & " " & LEFT(C2, 1) & "." даст «Каримов А.».

TEXTJOIN удобна и для сбора списка в одну ячейку — например, всех email команды через «; », чтобы вставить их в почтовый клиент.

Мгновенное заполнение: по примеру, без формул

Мгновенное заполнение (Flash Fill: «Данные» → «Мгновенное заполнение» или Ctrl+E) смотрит, что вы ввели в первые одну-две ячейки, и продолжает шаблон. Напишите «Азиз» рядом с «Каримов Азиз Бахтиёрович», нажмите Ctrl+E — и Excel заполнит имена для всего столбца.

Как выбрать:

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

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

• Разделять до очистки. Двойные пробелы дают пустые столбцы и сдвиг данных.
• Перезаписывать исходник. Оставьте исходный столбец и работайте рядом; в значения превращайте только в конце («Копировать» → «Специальная вставка» → «Значения»).
• Хранить телефоны как числа. Excel отбрасывает ведущий «+» и может показать длинное число в экспоненциальном виде. Храните номера как текст.
• Смешанные разделители. Запятые, точки с запятой и слэши в одном столбце. TEXTSPLIT принимает массив разделителей: =TEXTSPLIT(A2, {",", ";", "/"}).