Разложить слипшиеся значения по столбцам можно четырьмя способами, и различаются они тем, переживёт ли результат изменение исходных данных. Разбираю на мокапах мастер «Текст по столбцам» и две его ловушки — формат «Общий», который съедает ведущие нули у артикулов, и молчаливое затирание соседних столбцов, — а также мгновенное заполнение, формулы и Power Query.
- Мастер «Текст по столбцам» и Ctrl+E дают статичный результат, формулы и Power Query пересчитываются.
- Третий шаг мастера пропускать нельзя: формат «Общий» превращает 007123 в 7123, а 12.05 в дату.
- Разбивка ложится поверх соседних столбцов — вставьте пустые заранее или укажите «Поместить в:».
- Ctrl+E выводит правило по вашему примеру: годится там, где разделителя нет вовсе.
- В Excel 365 всё решает =ТЕКСТРАЗД(A2;", "), в Google Таблицах — =SPLIT(A2;",").
- Power Query окупается со второго раза, когда выгрузка приходит регулярно.
Четыре способа и как выбрать
Задача всегда выглядит одинаково: в одной ячейке слиплось несколько значений — город и адрес, артикул и название, ключ и частотность. Разложить их по столбцам в Excel можно четырьмя способами, и разница между ними в одном: пересчитается ли результат, когда исходные данные изменятся.
Для разовой задачи берите мастер, он же «Текст по столбцам». Для регулярной выгрузки — Power Query или формулы. Дальше разберу каждый способ и две ловушки мастера, из-за которых чаще всего теряют данные.
Мастер «Текст по столбцам»
Выделите столбец с данными и откройте Данные → Текст по столбцам. Мастер состоит из трёх шагов, и вся суть в третьем.
На втором шаге пригодится галочка «Считать последовательные разделители одним» — она нужна, когда данные выровнены пробелами и между значениями то один пробел, то восемь. Без неё каждый лишний пробел даст пустую колонку.
Ловушка первая: формат «Общий»
По умолчанию Excel сам решает, что за данные попали в столбец. Решает он агрессивно.
Отдельная беда с телефонами и кодами: Excel видит знак и превращает строку в формулу или число. Вернуть исходное значение потом невозможно, потому что данные уже перезаписаны. Поэтому правило простое: если в столбце есть что-то похожее на код, артикул, дату или номер, на третьем шаге кликните по нему в предпросмотре и поставьте «Текстовый».
Ловушка вторая: затёртые соседние столбцы
Результат разбивки ложится вправо от исходного столбца, поверх того, что там уже было.
Второй способ, через поле «Поместить в:», удобнее: исходный столбец остаётся нетронутым, а результат появляется в свободном месте. Если что-то пошло не так, можно просто удалить результат и повторить.
Ctrl+E: когда правила нет
Мгновенное заполнение появилось в Excel 2013 и решает задачи, которые мастеру не по зубам: достать из строки кусок по неочевидному правилу.
Работает это так. В соседнем столбце вручную пишете, что хотите получить из первой строки. Переходите на ячейку ниже и нажимаете Ctrl+E. Excel смотрит на пример, выводит правило и заполняет весь столбец.
Если Ctrl+E не сработал, дайте два-три примера вместо одного: правило станет однозначнее. Помогает и включить подсказку заранее — Данные → Мгновенное заполнение.
Формулы, ТЕКСТРАЗД и Power Query
Когда список пополняется и разбивка нужна постоянно, статичные способы не годятся. Дальше — формулы.
Классическая тройка работает в любой версии. Первая часть до запятой: =ЛЕВСИМВ(A2;НАЙТИ(",";A2)-1). Всё после последней запятой: =ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ("|";ПОДСТАВИТЬ(A2;",";"|";ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;",";""))))). Вторая формула выглядит устрашающе: в старых версиях Excel нет функции, которая разбивала бы строку целиком, и последнюю часть приходится вычислять через позицию последней запятой.
В Excel 365 появилась =ТЕКСТРАЗД(A2;", "): одна формула разом раскладывает строку по столбцам вправо и пересчитывается при изменении исходника. Если она доступна, остальные варианты можно не рассматривать.
В Google Таблицах то же самое делает =SPLIT(A2;",") — она есть с самого начала и тоже растекается вправо. Плюс в меню лежит готовый пункт Данные → Разделить текст на столбцы, аналог мастера.
Power Query нужен, когда разбивка — часть регулярного процесса: выгрузка приходит раз в месяц, и с ней каждый раз делают одно и то же. Импортируете файл, разбиваете столбец по разделителю в редакторе запросов, настраиваете типы — и при следующей выгрузке достаточно нажать «Обновить». Настройка займёт больше времени, чем разовая разбивка, и окупится со второго раза.
Фиксированная ширина в мастере — вариант для случая, когда разделителя нет вообще: выгрузка из старой учётной системы, лог, отчёт в моноширинном виде. На втором шаге вы просто расставляете границы столбцов мышью.
Когда разбивать быстрее онлайн
Отдельный случай — когда данных ещё нет в файле. Список пришёл в сообщении, в письме, скопирован из веб-интерфейса. Открывать ради него Excel и проходить мастер долго: быстрее разделить текст на столбцы онлайн. Инструмент разбивает по выбранному разделителю, показывает результат таблицей и отдаёт его с табуляцией между частями — при вставке в Excel такой текст сам ложится по ячейкам.
Два отличия от мастера, которые здесь работают в плюс. Первое: текст остаётся текстом, артикул с ведущими нулями не превращается в число. Второе: можно забрать одну колонку из строки — например, только домены из списка URL или только города из адресов, — не разбивая всё остальное.
В работе с семантикой разбивка нужна постоянно: выгрузки приходят строками вида «запрос;частотность;конкуренция», а дальше их надо чистить и группировать. Порядок такой: сначала убрать лишние пробелы, потом разделить на столбцы, потом удалить дубликаты и только затем кластеризовать. Если поменять первые два шага местами, хвостовые пробелы разъедутся по всем колонкам сразу.
Частые вопросы
Как разделить текст по столбцам в Excel?
Почему после разбивки пропали ведущие нули у артикулов?
Почему после «Текст по столбцам» исчезли соседние столбцы?
Как разбить текст по столбцам без мастера?
Что такое мгновенное заполнение и как им пользоваться?
Как разделить текст на столбцы в Google Таблицах?
Как разбить данные, если разделителя нет вообще?
Как разделить текст по столбцам онлайн?
Главное
Выбор способа сводится к одному вопросу: нужен ли живой результат. Разовая задача — мастер «Текст по столбцам», нестандартное правило без разделителя — Ctrl+E, регулярная выгрузка — Power Query, пополняемый список — формулы или ТЕКСТРАЗД. И две вещи, которые стоит помнить про мастер. Третий шаг задаёт формат столбцов и спасает артикулы, коды и даты от автоматического превращения. Результат ложится поверх соседних столбцов и стирает то, что там было.
Список пришёл текстом, а не файлом — разделите его на столбцы онлайн: разделитель на выбор, учёт кавычек, предпросмотр таблицей и результат, который вставляется в Excel готовыми ячейками.