Разделить текст: ФИО, адрес, артикул

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

Фамилия — всё до первого пробела

НАЙТИ определяет позицию пробела, ЛЕВСИМВ берёт символы до него.

=ЛЕВСИМВ(A2;НАЙТИ(" ";A2)-1)
English: =LEFT(A2,FIND(" ",A2)-1)

Имя — между первым и вторым пробелом

Второй пробел ищем, начиная с позиции сразу после первого. Формула выглядит громоздко, но логика простая: нашли начало, нашли конец, взяли кусок между ними.

=ПСТР(A2;НАЙТИ(" ";A2)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2)+1)-НАЙТИ(" ";A2)-1)
English: =MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2)-1)

Отчество — всё после второго пробела

Берём остаток строки от позиции второго пробела до конца.

=ПСТР(A2;НАЙТИ(" ";A2;НАЙТИ(" ";A2)+1)+1;100)
English: =MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,100)

Число 100 — заведомо большая длина, лишнего не захватится.

Короткий вариант для новых версий

В Excel 365 есть функции, которые берут текст до и после разделителя без вложенных поисков.

=TEXTBEFORE(A2;" ")
English: =TEXTBEFORE(A2," ")

Соответственно ТЕКСТПОСЛЕ вернёт остаток. В старых версиях этих функций нет.

Собрать обратно

Обратная задача решается амперсандом: склеиваем фамилию и инициалы.

=A2&" "&ЛЕВСИМВ(B2;1)&"."&ЛЕВСИМВ(C2;1)&"."
English: =A2&" "&LEFT(B2,1)&"."&LEFT(C2,1)&"."

Как это выглядит на данных

A (ФИО)ФамилияИмяОтчество
Иванов Иван ИвановичИвановИванИванович

При изменении исходной ячейки все три столбца пересчитаются автоматически.

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

Формула возвращает ошибку #ЗНАЧ!

В ячейке нет нужного пробела — например, записаны только фамилия и имя. Защититесь: оберните формулу в ЕСЛИОШИБКА и верните пустую строку.

В результат попадают лишние пробелы

В исходных данных встречаются двойные пробелы. Сначала прогоните столбец через СЖПРОБЕЛЫ, потом делите.

Вопросы и ответы

Есть ли способ без формул?

Да, вкладка «Данные» → «Текст по столбцам» → с разделителем «пробел». Минус в том, что это разовая операция: при изменении исходных данных придётся повторять.

Что такое мгновенное заполнение?

В Excel 2013 и новее достаточно вручную заполнить одну-две строки образца и нажать Ctrl+E — программа сама распознает шаблон и заполнит остальное.

Ваша задача отличается?

Опишите её словами — ExelGen соберёт формулу под ваши столбцы и версию Excel.

Сгенерировать формулу

Читайте также