Главная → Как сделать в Excel → Текст по столбцам

Текст по столбцам в Excel

Разбивает строку с разделителем на отдельные столбцы формулой — разбор переживает обновление данных, в отличие от разового мастера. ЛЕВСИМВ берёт первый кусок, ПСТР с заменой разделителя на пробелы — остальные.

Коротко

Приём раскладывает одну строку с разделителем на отдельные столбцы — формулой, а не разовым мастером. Адрес Москва;ул. Ленина;д. 5;кв. 12 становится четырьмя ячейками; ФИО с датой Петров;Иван;Сергеевич;1985 — тоже четырьмя; выгрузка TK-1045;Полка Nord;6200;41 — четырьмя. Поменяли исходную строку — части пересчитались сами.

Исходные строки лежат в столбце B начиная со строки 7, разделитель — в ячейке C4 (по умолчанию точка с запятой). Поставили в неё запятую или пробел — разбор идёт по ним.

Первый кусок берётся отдельной формулой через ЛЕВСИМВ и НАЙТИ: всё, что слева от первого разделителя. Остальные — приёмом с заменой разделителя на 200 пробелов: ПОДСТАВИТЬ раздвигает куски пробелами, а ПСТР вырезает нужный отрезок по счёту. Формулу тянут вправо, меняется только множитель — 1, 2, 3 по числу столбцов. Всё это лежит в файле-песочнице.

То же самое формулой в Excel

=ЕСЛИ($B7="";"";СЖПРОБЕЛЫ(ЛЕВСИМВ($B7;ЕСЛИОШИБКА(НАЙТИ($C$4;$B7)-1;ДЛСТР($B7)))))

— часть 1, до первого разделителя: ЛЕВСИМВ берёт всё слева от него, ЕСЛИОШИБКА оставляет строку целиком, если разделителя нет

=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($B7;$C$4;ПОВТОР(" ";200));1*200+1;200));"") — часть 2: разделитель заменён на 200 пробелов, ПСТР вырезает второй отрезок; начало — 1*200+1

=ЕСЛИОШИБКА(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ($B7;$C$4;ПОВТОР(" ";200));2*200+1;200));"") — часть 3: та же формула, начало сдвинуто на 2*200+1; тяните вправо — меняется только множитель

=ЕСЛИ($B$7="";"";ДЛСТР($B$7)-ДЛСТР(ПОДСТАВИТЬ($B$7;$C$4;""))+1) — число частей: длина строки минус её длина без разделителей плюс один — сколько столбцов заводить

Текст по столбцам: песочница на формулах

Вводите строку в жёлтую ячейку и разделитель рядом — части раскладываются по столбцам сразу, формулой. Первый кусок через ЛЕВСИМВ и НАЙТИ, остальные — заменой разделителя на пробелы и ПСТР. Отдельная ячейка показывает, на сколько частей разобьётся строка. Тянется вниз на новые строки без правок.

Забрать в Telegram Забрать в MAX

Файл tekst-po-stolbtsam.xlsx · формулы работают в Excel 2016 и новее и в Google Таблицах · без макросов · формулы проходят автопроверку перед публикацией

Лист файла «Текст по столбцам в Excel»: Исходная строка, Часть 1, Часть 2, Часть 3, Часть 4, Приём с ПОВТОР и ПОДСТАВИТЬ: разделитель заменяется на двести пробелов, потом из строки вырезается кусок фиксированной длины. Работает с любым числом частей. — Так выглядит файл. Картинка собрана из него же при сборке сайта: числа в ней те, что посчитает Excel.

Пошагово

Впишите исходную строку в жёлтую ячейку столбца B — например, в B7. Разделитель поставьте в C4: точку с запятой, запятую или пробел.
Первый кусок. В соседней ячейке: =ЕСЛИ($B7="";"";СЖПРОБЕЛЫ(ЛЕВСИМВ($B7;ЕСЛИОШИБКА(НАЙТИ($C$4;$B7)-1;ДЛСТР($B7))))). НАЙТИ находит первый разделитель, ЛЕВСИМВ берёт всё до него, ЕСЛИОШИБКА оставляет строку целиком, если разделителя в ней нет.
Следующие куски. Приём с 200 пробелами: ПОДСТАВИТЬ меняет разделитель на 200 пробелов, ПСТР вырезает отрезок по 200 символов, СЖПРОБЕЛЫ убирает лишние. Для второго куска начало — 1*200+1, для третьего — 2*200+1, дальше по образцу.
Закрепите разделитель. Ссылка на ячейку с разделителем идёт со знаком доллара — $C$4, чтобы при протягивании она не сползла. F4 ставит доллары одним нажатием.
Протяните. Ухватите ячейки за уголок и тяните вправо — по числу столбцов, и вниз — по числу строк. Множитель в ПСТР растёт сам: 1, 2, 3.
Сверьтесь с числом частей. =ЕСЛИ($B$7="";"";ДЛСТР($B$7)-ДЛСТР(ПОДСТАВИТЬ($B$7;$C$4;""))+1) показывает, на сколько кусков разобьётся строка — чтобы не завести столбцов меньше, чем нужно.

Где обычно ломается

Три места, где разбор ломается чаще всего:

Встроенный мастер разовый. «Данные → Текст по столбцам» разрежет строки один раз и про них забудет. Обновили выгрузку, дописали строку — мастер надо запускать заново. Формула пересчитывается сама, поэтому для живых данных берут её, а не мастер.
Мастер портит артикулы и коды. Значение вроде 05-2026 мастер молча посчитает датой, а 007 — числом 7: артикул превращается в дату, ведущие нули пропадают. Формула тип данных не трогает — что было текстом, текстом и остаётся.
Кусок длиннее 200 символов. Приём с заменой разделителя на 200 пробелов держится на том, что каждая часть короче 200 знаков. Если в поле бывает длинный адрес или примечание, поднимите число во всех ПОВТОР и ПСТР с 200 до 300 или 500 — одинаково во всех формулах.

Частые вопросы

Как в Excel сделать текст по столбцам

Встроенный мастер лежит в «Данные → Текст по столбцам»: указываете разделитель и получаете готовые столбцы. Минусов у него два. Он разовый — обновили выгрузку или дописали строки, и разбирать надо заново. И он молча меняет тип данных: «007» превращается в число 7, «05-2026» — в дату, ведущие нули и коды теряются, а вернуть их можно только из исходной выгрузки. Формульный разбор со страницы тип данных не трогает и пересчитывается сам.

Какая формула в Excel позволяет разделить текст по столбцам

Первый кусок берут ЛЕВСИМВ и НАЙТИ — всё, что слева от первого разделителя. Остальные — приёмом с ПОДСТАВИТЬ и ПСТР: разделитель меняется на 200 пробелов, куски расходятся далеко друг от друга, ПСТР вырезает нужный отрезок по счёту, а СЖПРОБЕЛЫ убирает лишнее. Готовые формулы — в блоке выше, кнопка копирует их сразу. Исходная строка при этом остаётся на месте: части появляются в соседних столбцах, а не поверх неё, — мастер же переписывает строку и до разреза уже не вернуться.

Как разделить ячейку в Excel по запятой

Поставьте запятую в ячейку с разделителем — в примере это C4 — вместо точки с запятой, и формулы разберут строку по ней. Запятую с пробелом «, » учитывать отдельно не нужно: СЖПРОБЕЛЫ в формуле сам срежет лишние пробелы по краям каждого куска.

Как разделить строку по разделителю в Excel

Разделителем может быть любой символ: точка с запятой, запятая, пробел, вертикальная черта. Он лежит в отдельной ячейке, формулы ссылаются на неё со знаком доллара — поменяли символ, и разбор пошёл по нему. Сколько выйдет частей, показывает =ЕСЛИ($B$7="";"";ДЛСТР($B$7)-ДЛСТР(ПОДСТАВИТЬ($B$7;$C$4;""))+1): длина строки минус её длина без разделителей даёт число разделителей, плюс один — это число частей. Удобно, чтобы завести ровно столько столбцов, сколько нужно.

Связанные страницы

Как сделать в Excel

Разделить ФИО на столбцы