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

Выгрузка из CRM или учётной системы часто кладёт в одну ячейку то, что нужно в трёх: фамилию, имя и отчество, город и улицу, логин и домен. Справка Microsoft описывает ТЕКСТДО как функцию, которая возвращает текст до разделителя, а ТЕКСТПОСЛЕ — как обратную к ней^[2]. Без формул такую строку режут вручную, и при новой выгрузке работа повторяется.

Разделить текст в Excel формулой выгоднее, чем вручную: формула пересчитывается, когда в выгрузку приходят новые строки. Путь одинаков для любой строки: найти разделитель, ввести формулу ТЕКСТРАЗД или ТЕКСТДО, проверить части на строке без отчества, исправить ошибку #Н/Д и протянуть формулу на свои данные. Главная ловушка — неполные строки: формула, рассчитанная на три слова, падает на двух.

Ключевые факты: разделить текст в Excel

• Разделение текста раскладывает одну строку на части по разделителю: пробелу, запятой, знаку @ или дефису.
• ТЕКСТРАЗД принимает 6 аргументов, обязательны первые 2: текст и разделитель столбцов^[1].
• ТЕКСТДО и ТЕКСТПОСЛЕ возвращают #Н/Д, если разделителя в строке нет^[2].
• ТЕКСТДО отличается от ТЕКСТРАЗД тем, что возвращает одну часть строки, а не все части сразу.
• Справка Microsoft относит ТЕКСТРАЗД к Microsoft 365 и Excel 2024, включая Mac^[1].

Как разделить текст в Excel функцией ТЕКСТРАЗД

Разделение текста формулой ТЕКСТРАЗД раскладывает строку по разделителю в соседние ячейки одной формулой. Синтаксис по справке Microsoft — ТЕКСТРАЗД(текст;разделитель_столбцов;[разделитель_строк];[игнорировать_пустые];[режим_сопоставления];[заполняющее_значение])^[1]. Все примеры страницы считаются на одном листе:

1. Выберите B2 и введите =ТЕКСТРАЗД(A2;" ").
2. Нажмите Enter — части разольются вправо: Иванов, Пётр, Сергеевич^[3].
3. Для адреса укажите запятую с пробелом: =ТЕКСТРАЗД(A3;", ") вернёт «Москва», «ул. Ленина» и «д. 5» без лишнего пробела в начале.

Справа от формулы нужны пустые ячейки: результат занимает столько столбцов, сколько частей в строке. Одну часть из массива достаёт ИНДЕКС: =ИНДЕКС(ТЕКСТРАЗД(A2;" ");1;3) вернёт «Сергеевич». Третий аргумент делит строку не по столбцам, а по строкам: для «1,2,3;4,5,6» формула =ТЕКСТРАЗД(A9;",";";") вернёт две строки по три значения. Несколько разделителей задают массивом — =ТЕКСТРАЗД("Арт.001-А";{".";"-"}) разрежет строку и по точке, и по дефису^[3]. Так можно разделить текст в Excel даже тогда, когда в выгрузке смешаны разные знаки.

Четвёртый аргумент управляет пустыми частями. Для строки «a b» с двойным пробелом формула без него даёт три ячейки со средней пустой, а с ИСТИНА — две^[3]. Пятый аргумент 1 отключает учёт регистра: по умолчанию стоит 0, и разделитель «И» не совпадёт с «и»^[1].

Части возвращаются строками: «д. 5» из адреса остаётся текстом, проверка ЕТЕКСТ это подтверждает^[3]. Для расчётов такую часть оборачивают в ЗНАЧЕН, иначе сумма по столбцу её не увидит.

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

Чем ТЕКСТДО отличается от ТЕКСТПОСЛЕ и как взять одну часть

ТЕКСТДО возвращает часть строки до заданного разделителя, ТЕКСТПОСЛЕ — после него, а номер экземпляра выбирает, какой разделитель считать. Синтаксис по справке Microsoft — ТЕКСТДО(текст;разделитель;[номер_экземпляра];[режим_сопоставления];[конец_сопоставления];[если_не_найдено]); у ТЕКСТПОСЛЕ аргументы те же^[2].

Отрицательный номер экземпляра запускает поиск с конца строки^[4]. Последнее слово строки достаёт ТЕКСТПОСЛЕ с номером экземпляра -1 при любом числе слов, поэтому формула не ломается на двойных фамилиях. Результаты таблицы пересчитаны в движке тренажёра^[3]. Вложенная запись для имени сначала отрезает фамилию функцией ТЕКСТПОСЛЕ, а затем берёт часть до следующего пробела. Такая пара функций разделит текст в Excel на нужный кусок без лишних столбцов: в таблицу попадает только фамилия или только домен.

Ошибки у обеих функций одинаковые, и проверять их стоит на строке без нужного разделителя. Если разделителя нет или номер больше числа вхождений, функция возвращает #Н/Д, а номер 0 или больше длины строки даёт #ЗНАЧ!^[2].

Однако в Excel 2021 и более ранних версиях этих функций нет, и задача решается иначе — классическими формулами.

Формулы для Excel 2021 и старше: НАЙТИ, ЛЕВСИМВ и ПСТР

Разделение текста без новых функций строится на позиции разделителя: НАЙТИ находит пробел, а ЛЕВСИМВ, ПСТР и ПРАВСИМВ вырезают части. Справка Microsoft разбирает такой способ на примерах имён и относит его ко всем версиям от Excel 2016 до Microsoft 365^[5].

Формулы читаются по шагам:

1. Найдите первый пробел: НАЙТИ(" ";A2) вернёт 7^[3].
2. Найдите второй пробел, начиная поиск со следующего знака: НАЙТИ(" ";A2;НАЙТИ(" ";A2)+1) вернёт 12.
3. Возьмите ЛЕВСИМВ до первого пробела, ПСТР между пробелами и ПРАВСИМВ после второго.

Для пробела, запятой и знака @ можно взять и ПОИСК: регистр у этих знаков не важен, а НАЙТИ отличается только учётом регистра букв. Минус единица в формуле фамилии убирает сам пробел: без неё в ячейке окажется «Иванов » с хвостом, и сравнение со справочником не сработает. Позиции и длину строки подробно разбирает страница о ЛЕВСИМВ, ПСТР и поиске позиции знака.

В Google Таблицах есть своя функция SPLIT. По умолчанию она режет текст по каждому знаку разделителя отдельно, поэтому для разделителя из запятой и пробела третий аргумент ставят FALSE^[6].

Именно поэтому строки неполной структуры требуют отдельного разбора: формула со вторым пробелом рассчитана ровно на три слова.

Пример: ФИО без отчества и пустые части

Разделение ФИО без отчества ломает формулу с поиском второго пробела: для строки «Петрова Анна» формула имени вернёт #ЗНАЧ!^[3]. Новые функции дают на той же строке #Н/Д, и у каждой ошибки есть своё исправление.

ТЕКСТДО с концом текста как разделителем возвращает всю строку там, где второго пробела нет: =ТЕКСТДО(A4;" ";2;;1) даёт «Петрова Анна»^[3]. Если нужен явный признак пропуска, последний аргумент подставит прочерк: =ТЕКСТДО(A4;" ";2;;;"—"). Прочерк удобен для отчёта: по нему фильтр сразу покажет строки без отчества, которые стоит проверить вручную.

Для старых версий запасной вариант собирают через ЕСЛИОШИБКА: =ЕСЛИОШИБКА(ПСТР(A4;НАЙТИ(" ";A4)+1;НАЙТИ(" ";A4;НАЙТИ(" ";A4)+1)-НАЙТИ(" ";A4)-1);ПРАВСИМВ(A4;ДЛСТР(A4)-НАЙТИ(" ";A4))). Формула длинная, но для «Петрова Анна» вернёт «Анна» в любой версии.

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

Остаётся понять, когда формула для разбора строки вообще не нужна или не сработает.

Когда разделение текста формулой не подойдёт

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

• Если файл откроют в Excel 2021 или старше, ТЕКСТРАЗД, ТЕКСТДО и ТЕКСТПОСЛЕ туда не перенесутся: в справке функции отнесены только к Microsoft 365 и Excel 2024^[1].
• При слитном тексте без разделителя, вроде «Москва123456», формулы по разделителю бессильны — нужна ЛЕВСИМВ с фиксированной длиной.
• Если справа от ТЕКСТРАЗД заняты ячейки, а это легко проверить до ввода формулы, массиву некуда разлиться, и вместо частей появится ошибка.
• Если файл открывают в Google Таблицах, формула с ТЕКСТРАЗД не сработает: там есть только SPLIT со своими правилами разделителя^[6].
• Когда выгрузку режут один раз, формулы лишние: справка Microsoft предупреждает, что разбор ячейки командой на ленте заменит данные следующей ячейки, поэтому место освобождают заранее^[7].

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