Как разделить текст по столбцам в Excel
Выгрузка из CRM записала фамилию, имя и отчество в одну ячейку, а для рассылки нужны отдельные столбцы. Или адреса слиплись через запятую, и фильтровать по городу невозможно. Переписывать сотни строк вручную незачем: Excel умеет разбивать такие данные за пару минут.
Ниже — три способа: мастер «Текст по столбцам» для разовой задачи, мгновенное заполнение для похожих строк и формулы, если данные продолжают обновляться.
- Для разовой задачи подходит Данные → Текст по столбцам.
- Если строки однотипные, используйте мгновенное заполнение: Ctrl+E.
- Если исходные данные меняются, нужны формулы:
ТЕКСТРАЗДилиЛЕВСИМВсНАЙТИ. - Заранее освободите столбцы справа: мастер запишет результат поверх существующих данных.
- Коды с нулями в начале задавайте форматом Текстовый, иначе нули пропадут.
Как выбрать способ
| Способ | Когда подходит | Обновляется при изменениях |
|---|---|---|
| Текст по столбцам | Один общий разделитель: пробел, запятая, точка с запятой | Нет |
| Мгновенное заполнение | Строки однотипные, нужно вытащить часть | Нет |
| Формулы | Данные будут дополняться или меняться | Да |
Перед началом сделайте копию листа или хотя бы столбца. Разделение выполняется поверх данных, и если что-то пойдёт не так, вы сможете вернуться к исходнику. Для отмены сразу после операции подходит Ctrl+Z.
Способ 1. Мастер «Текст по столбцам»
Допустим, в столбце A записано Иванова Анна Сергеевна. Справа пусть будут три свободных столбца: B, C и D, иначе мастер заменит то, что там есть.
- Выделите ячейки столбца A с данными.
- Откройте вкладку Данные и нажмите Текст по столбцам.
- Выберите С разделителями и нажмите Далее.
- Отметьте разделитель, в нашем случае Пробел. Предпросмотр снизу покажет будущие столбцы.
- Если в данных встречается несколько пробелов подряд, поставьте флажок Считать последовательные разделители одним.
- Нажмите Далее.
- Выберите формат для каждого столбца и в поле Поместить в укажите ячейку, например
$B$2, если хотите сохранить исходный столбец. - Нажмите Готово.

Если у записей разное количество слов, например «Анна-Мария Ли» или длинные названия компаний, часть значений окажется в чужом столбце. Проверьте результат сортировкой или посмотрите на последний столбец: в нём появится всё лишнее. Для таких случаев подойдут формулы.
Что выбрать на третьем шаге
- Общий — обычный вариант для чисел и текста.
- Текстовый — для кодов, артикулов и телефонов. Иначе
00123станет123. - Дата — если важен порядок дня и месяца, выберите нужный.
- Пропустить столбец — столбец не будет записан, если он вам не нужен.
Вариант Фиксированной ширины на первом шаге нужен для данных с равным количеством символов, например, если в выгрузке из старой системы код всегда занимает первые шесть знаков.
Способ 2. Мгновенное заполнение
Excel понимает задачу по примеру. Этот способ быстрее и обходится без мастера.
- Рядом с исходным столбцом введите нужный результат для первой строки. Например, рядом с
Иванова Анна СергеевнанапишитеИванова. - Перейдите в ячейку ниже и нажмите Ctrl+E или Данные → Мгновенное заполнение.
- Проверьте, как Excel заполнил столбец.
- Повторите для имени и отчества.
Так можно и собрать данные в другом виде, например превратить полное имя в «Иванова А. С.». Если Excel ошибся, поправьте ещё одну-две строки и нажмите Ctrl+E снова. Способ работает лучше на однородных данных и не обновляется при изменении исходной ячейки.
Способ 3. Формулы
Формулы подходят для таблиц, которые постоянно дополняются. В новых версиях Excel, например в Microsoft 365, есть функция, которая решает задачу сразу.
=ТЕКСТРАЗД(A2;" ")
Она разобьёт ячейку A2 по пробелу и заполнит соседние справа ячейки. Эти ячейки должны быть пустыми, иначе появится ошибка #ПЕРЕНОС!. Функция работает только в свежих версиях. Если её нет, используйте классические функции.
| Что нужно | Формула |
|---|---|
| Первое слово | =ЛЕВСИМВ(A2;НАЙТИ(" ";A2)-1) |
| Всё после первого пробела | =ПСТР(A2;НАЙТИ(" ";A2)+1;100) |
| Текст до запятой | =ЛЕВСИМВ(A2;НАЙТИ(",";A2)-1) |
| Убрать лишние пробелы перед разбором | =СЖПРОБЕЛЫ(A2) |
Если разделителя в ячейке нет, НАЙТИ вернёт ошибку #ЗНАЧ!. Оберните формулу так: =ЕСЛИОШИБКА(ЛЕВСИМВ(A2;НАЙТИ(" ";A2)-1);A2), и тогда для таких строк покажется исходный текст.
Чтобы превратить результат в обычные значения, скопируйте столбец и выберите Специальная вставка → Значения. После этого исходный столбец можно удалять.
Если разделение пошло не так
- Числа и даты превратились в чужие. Например,
1.05Excel понял как дату. Отмените и задайте формат Текстовый на третьем шаге. - Значения съехали. В строке оказался двойной пробел. Отметьте Считать последовательные разделители одним или сначала примените
СЖПРОБЕЛЫ. - Кнопка недоступна. Мастер работает с одним столбцом за раз, и с общим доступом к книге или на защищённом листе может быть выключен.
- Данные оказались в CSV. Если весь файл лежит в одном столбце через точку с запятой, это задача того же мастера: выберите Точка с запятой как разделитель.
Для одной выгрузки хватит Текста по столбцам. Если таблица живёт и пополняется, потратьте несколько минут на формулы.
Частые вопросы
Как разделить ячейку на две строки, а не на столбцы?
Мастер этого не делает. В Microsoft 365 можно использовать третий аргумент ТЕКСТРАЗД: =ТЕКСТРАЗД(A2;;",") разобьёт текст по запятой на строки. В других версиях удобнее Power Query.
Как соединить разделённые столбцы обратно?
Формула =A2&" "&B2&" "&C2 соберёт ФИО в одну строку. В новых версиях есть функция ОБЪЕДИНИТЬ.
Что делать, если строка разбита переносами внутри ячейки?
Выберите разделитель Другой, поставьте в его поле курсор и нажмите Ctrl+J. Символ не будет виден, но Excel разделит текст по переносу строки.
© kak2.ru



Оставить комментарий