Как использовать TEXTBEFORE, TEXTAFTER и TEXTSPLIT в Microsoft Excel

Быстрая навигация

• TEXTBEFORE
• TEXTAFTER
• TEXTSPLIT

Введение

Microsoft Excel добавил несколько функций для работы с текстом, которые упрощают извлечение подстрок и разбивку длинных строк на ячейки. Эти функции стали доступны начиная с августа 2022 года и постепенно распространяются среди пользователей Office. Они заменяют часто громоздкие комбинации с LEFT/RIGHT/MID, FIND/SEARCH и вспомогательными формулами.

Кратко о каждой функции:

• TEXTBEFORE — возвращает часть строки, расположенную до первого (или указанного) разделителя.
• TEXTAFTER — возвращает часть строки, расположенную после разделителя.
• TEXTSPLIT — разбивает строку на массив по разделителям по столбцам и/или строкам.

Важно: все примеры в статье используют текст в ячейке A2. Подставьте ваш адрес ячейки при применении формул.

TEXTBEFORE — синтаксис и примеры

Синтаксис:

TEXTBEFORE(text, delimiter, instance, match_mode, match_end, if_not_found)

• text — строка или ссылка на ячейку с текстом (обязательный).
• delimiter — разделитель, до которого нужно взять текст (обязательный).
• instance — номер вхождения разделителя (если несколько) — необязательный.
• match_mode — 0 для чувствительного к регистру поиска, 1 для нечувствительного; по умолчанию 0.
• match_end — 0 чтобы не учитывать совпадение в конце текста, 1 чтобы учитывать; по умолчанию 1.
• if_not_found — значение, которое возвращается, если разделитель не найден; по умолчанию возвращается ошибка.

Примеры:

Извлечь всё до слова “from”:

=TEXTBEFORE(A2,"from")

Извлечь всё до второго вхождения слова “text”:

=TEXTBEFORE(A2,"text",2)

С учётом регистрозависимости (найти только точное “TEXT”):

=TEXTBEFORE(A2,"TEXT",,0)

Совет: если разделитель отсутствует и вы хотите вернуть исходный текст вместо ошибки, используйте аргумент if_not_found:

=TEXTBEFORE(A2,"|",1,0,1,A2)

TEXTAFTER — синтаксис и примеры

TEXTAFTER — зеркальная функция для получения части строки после разделителя.

Синтаксис и опции идентичны TEXTBEFORE:

TEXTAFTER(text, delimiter, instance, match_mode, match_end, if_not_found)

Примеры:

Извлечь всё после слова “from”:

=TEXTAFTER(A2,"from")

Извлечь всё после второго вхождения слова “text”:

=TEXTAFTER(A2,"text",2)

Чувствительность к регистру:

=TEXTAFTER(A2,"TEXT",,0)

Практическое применение: быстрое выделение значения параметра из строки запроса, домена из email, или описания товара после кода.

TEXTSPLIT — синтаксис и примеры

TEXTSPLIT разбивает строку на массив, который Excel автоматически развернёт по столбцам или строкам.

Синтаксис:

TEXTSPLIT(text, column_delimiter, row_delimiter, ignore, match_mode, pad_with)

• text — строка или ссылка на ячейку (обязательный).
• column_delimiter — разделитель для столбцов (вставьте в кавычках) или оставьте пустым.
• row_delimiter — разделитель для строк (можно использовать вместо column_delimiter).
• ignore — TRUE (по умолчанию) чтобы игнорировать пустые элементы; FALSE чтобы создавать пустые ячейки при соседних разделителях.
• match_mode — чувствительность к регистру; по умолчанию чувствительный.
• pad_with — значение для заполнения при неравной длине массивов; иначе появится ошибка #N/A.

Примеры:

Разбить по пробелу в столбцы:

=TEXTSPLIT(A2," ")

Разбить по пробелу в строки:

=TEXTSPLIT(A2,," ")

Разбить по точке с запятой в столбцы:

=TEXTSPLIT(A2,";")

Разбить по точке с запятой в строки:

=TEXTSPLIT(A2,,";")

Совет: сочетайте TEXTSPLIT с TRIM и CLEAN для очистки пробелов и непечатаемых символов до или после разбивки.

Когда эти функции работают плохо или дают неожиданный результат

Важно:

• Разделитель не найден — по умолчанию функция вернёт ошибку. Используйте аргумент if_not_found, чтобы вернуть альтернативное значение.
• Разные региональные настройки — некоторые символы (например, запятая как десятичный разделитель) могут влиять на ввод литеральных значений в формулах; в сомнительных случаях используйте кавычки и явное указание строк.
• Массивы и таблицы — TEXTSPLIT возвращает массив. Если рядом есть данные, Excel может не «развернуть» массив корректно и появится ошибка затухания массива; убедитесь, что целевая область пуста.
• Производительность — при больших объёмах данных (десятки тысяч строк) вычисления массивов могут замедлить книгу; в таких случаях рассмотрите Power Query.

Альтернативные подходы

• LEFT/RIGHT/MID + FIND/SEARCH: полезно, если вы используете старые версии Excel без новых функций.
• Flash Fill (Автозаполнение по образцу): быстрый интерактивный способ для однотипных преобразований, но не автоматизированный при изменении исходных данных.
• Power Query: лучше для пакетной обработки больших наборов данных, сложного парсинга и подготовки ETL-пайплайнов.

Практические приёмы и эвристики

• Ментальная модель: думайте о TEXTBEFORE как о «взгляде влево до разделителя», TEXTAFTER как о «взгляде вправо», TEXTSPLIT как о «ножнице», разрезающей строку по шаблону.
• Для устойчивости формул всегда указывайте if_not_found, особенно если данные приходят из внешних источников.
• Если есть вариативные разделители (например, и запятая, и точка с запятой), предварительно замените их на единый символ с помощью SUBSTITUTE, либо используйте несколько вызовов TEXTSPLIT последовательно.

Чек-листы по ролям

Аналитик данных:

• Убедиться, что целевая область для развёртывания массива пуста.
• Добавить проверку наличия разделителя и альтернативное значение (if_not_found).
• Протестировать на пробельных и не ASCII-символах.

Контент-менеджер / Маркетолог:

• Использовать TEXTSPLIT для парсинга CSV, тегов и метаданных.
• Привести результаты к единому регистру с помощью UPPER/LOWER при необходимости.

Разработчик отчётов менеджмента:

• Автоматизировать очистку с TRIM и SUBSTITUTE перед разбивкой.
• Проверять формат вывода в сводных таблицах.

Краткая методология применения (шаги)

1. Проанализируйте входной текст и определите разделитель(и).
2. Протестируйте формулу на примерах (1–5 строк) и проверьте крайние случаи.
3. Добавьте аргумент if_not_found для обработки неожиданных значений.
4. Очистите результат (TRIM, CLEAN) и при необходимости приведите к нужному регистру.
5. При больших объёмах данных рассмотрите Power Query вместо массивных расчётов формул.

Краткий словарь (1 строка для терминов)

• Разделитель — символьный шаблон, по которому выполняется разбивка строки.
• Развёртывание массива — автоматическое размещение 반환яемого массива в соседних ячейках Excel.

Технические подсказки и шаблоны

Вернуть домен из email (после знака @):

=TEXTAFTER(A2,"@")

Взять имя пользователя до точки в имени файла (до первого “.”):

=TEXTBEFORE(A2,".")

Разбить список тегов, разделённых запятой, и удалить лишние пробелы:

=TEXTSPLIT(TRIM(SUBSTITUTE(A2,", ",",")),",")

Обработать ситуацию с пустыми элементами между двумя разделителями (сохранить пустые ячейки):

=TEXTSPLIT(A2,",",,FALSE)

Совместимость и миграция

• Эти функции появились в Excel начиная с августа 2022 года и постепенно распространяются. Если вы работаете с коллегами на разных версиях Excel, проверьте доступность функций (или используйте альтернативы и запасные формулы).
• Для автоматизированной обработки больших объёмов данных лучше мигрировать в Power Query, если целевая аудитория до сих пор пользуется старыми версиями Excel.

Критерии приёмки

• Формулы возвращают ожидаемые значения для базовых, граничных и пустых входных данных.
• Нет конфликтов с соседними данными при развёртывании массива.
• Обработаны случаи отсутствия разделителя (if_not_found) и лишних пробелов.

Когда эти функции не подойдут

• Вам нужно поддерживать очень старые версии Excel, где новых функций нет.
• Обработка огромных объёмов данных с множественными массивными формулами — тогда лучше Power Query или внешняя обработка.

Заключение

TEXTBEFORE, TEXTAFTER и TEXTSPLIT заметно упрощают ежедневные операции по парсингу и очистке текста в Excel. Они сокращают количество вспомогательных формул и делают листы более читабельными. Используйте их совместно с TRIM, SUBSTITUTE и CLEAN, чтобы получить надёжный и устойчивый результат.

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

Короткое резюме: эти функции — быстрый путь для извлечения частей строк и разбивки текстов; они полезны в аналитике, подготовке данных и при обработке CSV-текстов.