Разделить ячейку на строки в Excel значит взять ячейку со списком — «морковь, лук, картофель» — и разложить значения вниз, по одному в строке. Для одной ячейки хватит формулы =ТЕКСТРАЗД(A2;;", ") в Excel 365 или 2024. Если же делить нужно целый столбец, а рядом с каждым значением должна остаться его категория, используйте Power Query или процедуру «Развернуть по разделителю» надстройки !SEMTools: выделите два столбца, укажите разделитель — и таблица развернётся на новом листе.
Мастер «Текст по столбцам» здесь не поможет: он умеет раскладывать части только вправо, по столбцам. Ниже — все рабочие способы, от простого к мощному.
Одна ячейка на строки — функция ТЕКСТРАЗД
У функции ТЕКСТРАЗД есть третий аргумент — разделитель строк. Если второй аргумент оставить пустым, части уйдут не вправо, а вниз.
| A | B | |
|---|---|---|
| 1 | Список | Значения |
| 2 | морковь, лук, картофель | морковь=ТЕКСТРАЗД(A2;;", ") |
| 3 | лук | |
| 4 | картофель |
Две точки с запятой подряд — пропущенный разделитель столбцов. Разделитель «, » записан с пробелом, чтобы в начале значений не осталось пробелов.
В английском Excel: =TEXTSPLIT(A2,,", ")
Если внутри ячейки значения разделены переносом строки (Alt+Enter), разделитель — СИМВОЛ(10): =ТЕКСТРАЗД(A2;;СИМВОЛ(10)).
Таблицу с категориями на строки — формула Excel 365
Сложнее, когда ячеек много и у каждой есть «ключ» в соседнем столбце: категория, артикул, имя клиента. Нужно, чтобы после разбивки ключ повторялся в каждой новой строке. В Excel 365 и 2024 это можно сделать одной формулой: каждая запятая заменяется на «;ключ|», строки склеиваются через ОБЪЕДИНИТЬ, а ТЕКСТРАЗД режет результат сразу на строки и столбцы.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Категория | Значения | Категория | Значение | |
| 2 | Овощи | морковь, лук, картофель | Овощи=ТЕКСТРАЗД(ОБЪЕДИНИТЬ(";";ИСТИНА;A2:A4&"|"&ПОДСТАВИТЬ(B2:B4;", ";";"&A2:A4&"|"));"|";";") | морковь | |
| 3 | Фрукты | яблоко, груша | Овощи | лук | |
| 4 | Ягоды | малина | Овощи | картофель | |
| 5 | Фрукты | яблоко | |||
| 6 | Фрукты | груша | |||
| 7 | Ягоды | малина |
Для «Овощи» ПОДСТАВИТЬ превращает список в «морковь;Овощи|лук;Овощи|картофель», а перед ним добавляется «Овощи|». Все строки склеиваются через «;», и ТЕКСТРАЗД делит их: «;» — на строки, «|» — на два столбца. Символов «|» и «;» не должно быть в самих данных. Итоговая строка ОБЪЕДИНИТЬ ограничена 32 767 символами — для больших таблиц берите Power Query или надстройку.
В английском Excel: =TEXTSPLIT(TEXTJOIN(";",TRUE,A2:A4&"|"&SUBSTITUTE(B2:B4,", ",";"&A2:A4&"|")),"|",";")
Разделить ячейку на строки через Power Query
Power Query встроен в Excel 2016 и новее и не ограничен размером строки. Порядок действий:
- Щёлкните в таблице и выберите Данные → Из таблицы/диапазона. Excel предложит оформить данные как таблицу — согласитесь.
- В редакторе Power Query выделите столбец со списками и нажмите Главная → Разделить столбец → По разделителю.
- Укажите разделитель (например, «Пользовательский» и «, »), раскройте «Дополнительные параметры» и выберите Разделить на: строки.
- Нажмите Главная → Закрыть и загрузить. Результат появится на новом листе.
Плюс способа — запрос можно обновить (Данные → Обновить всё), когда исходная таблица изменится. Минус — много шагов для разовой задачи, а лишние пробелы после разделения придётся убирать отдельной командой «Формат → Усечь».
«Развернуть по разделителю» в !SEMTools
В надстройке !SEMTools та же задача решается в два клика и в любой версии Excel, начиная с 2010:
- Выделите два столбца: слева ключ (категория, артикул, клиент), справа ячейки со списками.
- На вкладке !SEMTools откройте ИЗМЕНИТЬ → Диапазон → Быстрая сводная → Развернуть по разделителю.
- Введите разделитель — так, как он записан в ячейках, вместе с пробелом, если он есть: «, » или «;».
- Нажмите «ОК». Надстройка создаст новый лист и выведет на него пары «ключ — значение», по одной в строке.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Исходный лист: A | B | Новый лист: A | B | |
| 2 | Овощи | морковь, лук, картофель | Овощи | морковь | |
| 3 | Фрукты | яблоко, груша | Овощи | лук | |
| 4 | Ягоды | малина | Овощи | картофель | |
| 5 | Фрукты | яблоко | |||
| 6 | Фрукты | груша | |||
| 7 | Ягоды | малина |
Слева — выделенные столбцы, справа — что получится на новом листе при разделителе «, ». Исходные данные не меняются.
Несколько особенностей процедуры:
- выделять нужно ровно два столбца, иначе надстройка попросит выделить два;
- разделитель ищется точно: если в ячейках «морковь, лук», а вы ввели «,» без пробела, у значений останется пробел в начале — его уберёт удаление лишних пробелов;
- если в ячейке два разделителя подряд, между ними получится пустое значение — отфильтруйте его;
- строку заголовков можно выделять вместе с данными: заголовок просто перенесётся на новый лист первой строкой;
- процедура есть в версиях !SEMTools Lite и Pro.
Обратная операция — собрать значения одной категории обратно в одну ячейку через запятую — находится в том же меню: «Быстрая сводная» → «Сцепить значения».
Какой способ выбрать
| ТЕКСТРАЗД | Формула с ОБЪЕДИНИТЬ | Power Query | !SEMTools | |
|---|---|---|---|---|
| Сохраняет ключ в каждой строке | нет | да | да | да |
| Обновляется при изменении данных | да | да | по кнопке «Обновить» | нет |
| Ограничение объёма | одна ячейка | 32 767 символов | нет | нет |
| Версии Excel | 365, 2024 | 365, 2024 | 2016+ | 2010+ |
| Шагов | 1 формула | 1 формула | 4–5 | 2 клика |
Похожие задачи
- Как разделить ячейку в Excel — по столбцам, ФИО, по диагонали, объединённые ячейки.
- Разбить лист по значению столбца — разнести строки таблицы по разным листам.
- Собрать все столбцы в один — когда значения разложены по столбцам, а нужен один список.
- Разделить текст и цифры внутри ячейки.
Почему ячейка не делится на строки — частые ошибки
- «Текст по столбцам» раскладывает только вправо. Для строк используйте способы из этой статьи.
- ТЕКСТРАЗД вернула #ПЕРЕНОС!. Ниже формулы есть заполненные ячейки — очистите место для результата.
- ТЕКСТРАЗД вернула #ИМЯ?. Функции нет в Excel 2021 и раньше — используйте Power Query или надстройку.
- Формула с ОБЪЕДИНИТЬ вернула #ЗНАЧ!. Склеенная строка длиннее 32 767 символов — разбейте таблицу на части или используйте Power Query.
- В начале значений пробел. Разделитель указан без пробела, а в данных он есть: «, », а не «,».
- Значения не разделились совсем. В ячейках другой разделитель — например, перенос строки: используйте СИМВОЛ(10).
Как разделить ячейку на строки — частые вопросы
Как разделить ячейку на несколько строк в Excel?
Для одной ячейки — формулой =ТЕКСТРАЗД(A2;;", ") (Excel 365 и 2024). Для таблицы — Power Query («Разделить столбец → По разделителю → Разделить на: строки») или «Развернуть по разделителю» в !SEMTools.
Можно ли разделить ячейку на строки через «Текст по столбцам»?
Нет, мастер раскладывает части только по столбцам. Можно разделить по столбцам, а затем транспонировать результат, но для таблицы с ключами это неудобно.
Как сохранить категорию в каждой строке после разделения?
Используйте Power Query, процедуру «Развернуть по разделителю» в !SEMTools или формулу Excel 365 с ТЕКСТРАЗД и ОБЪЕДИНИТЬ из этой статьи.
Как разделить ячейку по переносу строки?
Разделитель переноса строки — СИМВОЛ(10): =ТЕКСТРАЗД(A2;;СИМВОЛ(10)).
Как собрать строки обратно в одну ячейку?
Формулой ОБЪЕДИНИТЬ или процедурой «Быстрая сводная → Сцепить значения» в !SEMTools.
Развернуть таблицу по разделителю, собрать значения обратно в ячейку или построить сводную по категориям — без Power Query и формул, в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как разделить ячейку на строки
| Задача | Способ |
|---|---|
| Одна ячейка | =ТЕКСТРАЗД(A2;;", ") |
| По переносу строки | =ТЕКСТРАЗД(A2;;СИМВОЛ(10)) |
| Таблица с категориями, Excel 365 | ТЕКСТРАЗД + ОБЪЕДИНИТЬ + ПОДСТАВИТЬ |
| Большая таблица с обновлением | Power Query → Разделить на строки |
| Быстро и в любой версии | !SEMTools → Развернуть по разделителю |
Надеюсь, на этой странице вы нашли то, что искали и смогли разделить ячейки на строки. Для разбивки по столбцам загляните в статью как разделить ячейку в Excel, а все параметры функции разобраны на странице ТЕКСТРАЗД.