Мастер работы с текстом в Excel: от разбора ФИО до склеивания строк

Чтобы разделить текст в ячейке на части (например, ФИО на фамилию, имя и отчество), используйте функцию ТЕКСТРАЗД (или TEXTSPLIT в английской версии) в новых версиях Excel. Для объединения данных обратно применяйте ТЕКСТОБЪЕД (TEXTJOIN). В старых версиях (до 2019 года) эти задачи решаются комбинацией функций ЛЕВСИМВ, ПСТР, НАЙТИ и сцепкой через амперсанд (&).

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

Разделение текста на слова и части

Задача разбить одну ячейку на несколько столбцов встречается часто при импорте данных. Подход зависит от версии вашего табличного процессора.

Современный способ (Excel 365, 2021+)

Функция ТЕКСТРАЗД автоматически создает динамический массив. Вам не нужно протягивать формулу — она «разольется» сама.

Синтаксис: =ТЕКСТРАЗД(текст; разделитель_столбцов; [разделитель_строк])

Примеры использования:

• Разбить по пробелам: =ТЕКСТРАЗД(A2; " ")
  • Если в A2 написано «Иванов Иван», результат займет две ячейки справа.
• Несколько разделителей: Если текст разделен запятыми, точками с запятой или дефисами, перечислите их в кавычках: =ТЕКСТРАЗД(A2; ",;-")
• Извлечение конкретного слова: Чтобы получить только второе слово (например, имя), оберните формулу в функцию ИНДЕКС: =ИНДЕКС(ТЕКСТРАЗД(A2; " "); 2)

Классический способ (Все версии Excel)

В старых версиях нет динамических массивов, поэтому каждое слово извлекается отдельной формулой. Главная сложность — найти позицию пробела.

1. Первое слово (Фамилия): Находим первый пробел и берем всё, что слева от него. =ЛЕВСИМВ(A2; НАЙТИ(" "; A2 & " ") - 1)

Лайфхак: Прибавление " " к концу ячейки (A2 & " ") защищает формулу от ошибки, если в ячейке всего одно слово без пробелов.

2. Второе слово (Имя): Берем текст между первым и вторым пробелом. =ПСТР(A2; НАЙТИ(" "; A2) + 1; НАЙТИ(" "; A2 & " "; НАЙТИ(" "; A2) + 1) - НАЙТИ(" "; A2) - 1)

3. Третье слово (Отчество): Аналогично, но ищем третий пробел. Формула становится громоздкой, поэтому для старых версий часто проще использовать инструмент «Текст по столбцам» на вкладке Данные.

Разбор ФИО: нюансы и автоматизация

Работа с русскими именами имеет свои особенности: наличие отчества, инициалы с точками или без, двойные фамилии.

Сценарий 1: Полное ФИО («Иванов Иван Иванович»)

Используйте функцию ТЕКСТРАЗД для мгновенного получения трех столбцов. =ТЕКСТРАЗД(A2; " ") Результат автоматически распределится: Фамилия | Имя | Отчество.

Сценарий 2: Инициалы («Иванов И. И.»)

Если форматирование неоднородно (где-то полное имя, где-то инициалы), логика усложняется.

• Фамилия: Всегда первое слово до пробела. =ЛЕВСИМВ(A2; НАЙТИ(" "; A2 & " ") - 1)
• Инициалы/Имя: Всё, что осталось после первого пробела. =ПСТР(A2; НАЙТИ(" "; A2) + 1; 99)

Сценарий 3: Проверка количества слов

Перед обработкой полезно понять структуру данных. Посчитайте количество пробелов: =ДЛСТР(A2) - ДЛСТР(ПОДСТАВИТЬ(A2; " "; ""))

• Результат 1: Только Фамилия и Имя.
• Результат 2: Полное ФИО.
• Результат >2: Вероятно, двойная фамилия или ошибка в данных.

Извлечение частей строки по позиции

Часто нужно вытащить код, номер договора или дату из смешанной строки, например: «Заказ №123-А от 01.01.2026».

Для поиска позиции можно использовать функцию ПОИСК вместо НАЙТИ, если регистр букв не важен (ПОИСК игнорирует регистр).

Объединение текста в одну ячейку

Обратная операция — склеивание разрозненных данных.

Простая сцепка (Любая версия)

Используйте амперсанд &. =A2 & " " & B2 & " " & C2 Недостаток: если одна из ячеек пуста, появятся лишние пробелы («Иванов »).

Умное объединение (Excel 2019+, 365)

Функция ТЕКСТОБЪЕД (TEXTJOIN) решает проблему пустых ячеек и позволяет задать единый разделитель.

Синтаксис: =ТЕКСТОБЪЕД(разделитель; игнорировать_пустоты; диапазон)

Пример сборки ФИО: =ТЕКСТОБЪЕД(" "; ИСТИНА; A2:C2)

• " " — разделитель (пробел).
• ИСТИНА — пропускать пустые ячейки (если нет отчества, лишнего пробела не будет).
• A2:C2 — диапазон с Фамилией, Именем и Отчеством.

Эта функция идеально подходит для создания списков через запятую: =ТЕКСТОБЪЕД(", "; ИСТИНА; A2:A10) → «Яблоки, Груши, Сливы».

Частые ошибки и способы их избежать

1. Ошибка #ЗНАЧ! при отсутствии разделителя.

   • Причина: Функция НАЙТИ не находит указанный символ (например, пробел в однословной ячейке).
   • Решение: Оберните формулу в ЕСЛИОШИБКА(...; "") или используйте трюк с добавлением разделителя к концу строки (A2 & " ").

2. Лишние пробелы после импорта.

   • Данные из 1С или веб-сайтов часто содержат неразрывные пробелы (код 160), которые обычный СЖПРОБЕЛЫ не видит.
   • Решение: Используйте замену: =ПОДСТАВИТЬ(A2; СИМВОЛ(160); " "), а затем СЖПРОБЕЛЫ.

3. Разный регистр букв.

   • После разделения ФИО может выглядеть как «иванов иван».
   • Решение: Функция ПРОПНАЧ (PROPER) делает первую букву заглавной: =ПРОПНАЧ(A2).

FAQ

Как разделить текст по столбцам без формул? Выделите ячейки, перейдите на вкладку Данные → Текст по столбцам. Выберите «с разделителями», укажите пробел или запятую. Это статическое действие, формулы создаваться не будут.

Можно ли разделить текст на строки (вертикально)? Да, в новых версиях используйте третий аргумент ТЕКСТРАЗД. Например, =ТЕКСТРАЗД(A1; ; СИМВОЛ(10)) разделит текст по переносам строк внутри ячейки, выводя результаты вниз.

Что делать, если в ФИО двойная фамилия (Де Ла Порт)? Автоматическое разделение по пробелам даст сбой. В таких случаях надежнее использовать разделение по фиксированной ширине (инструмент «Текст по столбцам») или вручную корректировать данные, так как логика «первое слово — фамилия» здесь не работает.