Разделение данных в ячейке Excel на несколько столбцов¶
Разделение данных в ячейке Excel — это набор приёмов и инструментов электронной таблицы Microsoft Excel, позволяющих разбить текст, объединённый в одной ячейке, на отдельные ячейки (столбцы или строки). Наиболее распространённая задача — разделение полного ФИО, адреса или составного значения, объединённого разделителем (запятой, точкой с запятой, пробелом, табуляцией), на отдельные столбцы.
¶Инструмент «Текст по столбцам»
Встроенный мастер «Текст по столбцам» (в русской версии Excel; в английской — Text to Columns) — основной способ разделить содержимое ячейки на несколько столбцов. Команда находится на вкладке «Данные» на ленте Excel.
¶Пошаговый порядок действий
- Выделить ячейки (или столбец), которые требуется разделить.
- На вкладке «Данные» нажать кнопку «Текст по столбцам».
- В мастере выбрать тип данных:
- С разделители — если значения разделяются постоянным символом (запятая, точка с запятой, пробел, табуляция);
- Фиксированная ширина — если данные расположены в строгих колонках одинаковой ширины (типично для выгрузок из АСУ, бухгалтерских систем).
- Указать разделители или границы столбцов.
- Задать формат данных для каждого нового столбца (обычный, дата, валюта, текст).
- Задать место размещения результата — по умолчанию исходные ячейки перезаписываются, но можно указать другой столбец.
¶Ограничения
- «Текст по столбцам» работает только в пределах одного столбца — разделение идёт по горизонтали.
- Исходные данные при запуске перезаписываются, если не указать иное место назначения.
- Инструмент не динамический: при изменении исходных данных результат не обновляется автоматически.
¶Функция РАЗДЕЛИТЬТЕКСТ (TEXTSPLIT)
В версиях Excel для Microsoft 365 и Excel 2021 появилась динамическая функция РАЗДЕЛИТЬТЕКСТ (в английской версии — TEXTSPLIT). Она разбивает текст по указанному разделителю и возвращает результат в виде массива значений, которые автоматически «разливаются» по соседним ячейкам (функция динамических массивов).
Синтаксис: РАЗДЕЛИТЬТЕКСТ(текст; разделитель_по_столбцам; [разделитель_по_строкам]; …)
Пример: =РАЗДЕЛИТЬТЕКСТ(A1; ", ") разобьёт содержимое ячейки A1 по запятой с пробелом.
Преимущество — результат обновляется автоматически при изменении исходного значения. Недостаток — функция доступна не во всех версиях Excel.
¶Формульные способы разделения
Для версий Excel без поддержки динамических массивов разделение выполняется набором классических функций.
¶Разделение по разделителю
Для текста вида Иванов Иван Иванович (разделитель — пробел) можно использовать комбинацию:
- ЛЕВСТР (LEFT) — извлекает первое слово до первого пробела;
- ПОДСТРОКА (MID) + НАЙТИ (FIND) — извлекает второе слово;
- ПРАВСТР (RIGHT) + ДЛСТР (LEN) — извлекает последнее слово.
Альтернатива — функция ПОИСКПОЗ (MATCH) или ПОДСТРОКА с вычислением позиции разделителя.
¶Разделение по номеру символа
Если данные имеют фиксированную структуру (например, номер документа 0000-000000), используется ПОДСТРОКА:
=ПОДСТРОКА(A1; 1; 4) — первые 4 символа; =ПОДСТРОКА(A1; 6; 6) — следующие 6 символов.
¶Разделение по строкам
Для переноса текста внутри ячейки (символ Ctrl+Enter при вводе) используется функция ПЕРЕНОССТРОК (CHAR(10)) как разделитель. В мастере «Текст по столбцам» перенос строки выбирается автоматически при снятии флажков с остальных разделителей.
¶Power Query
Для массовой обработки больших объёмов данных используется Power Query (в русской версии — «Конструктор запросов», вкладка «Данные» → «Получить данные»). Возможности:
- Разделить столбец по разделителю или по числу символов;
- Удалить первые/последние символы;
- Разделить по маркерам (например, по первой заглавной букве);
- Результат оформляется как таблица, которую можно обновить одной кнопкой.
Power Query поддерживает сложные сценарии: удаление лишних пробелов, приведение типов, объединение нескольких столбцов обратно.
¶Объединение обратно
Обратная операция — объединение данных из нескольких ячеек в одну — выполняется функцией СЦЕПИТЬ (CONCAT в новых версиях) или ОБЪЕДИНАТЬТЕКСТ (TEXTJOIN), например: =СЦЕПИТЬ(A1; " "; B1; C1).
¶Типичные ошибки
- При разделении по пробелу в адресах с двойными пробелами появляются пустые ячейки — рекомендуется предварительно применить функцию СЖПРОБЕЛЫ (TRIM).
- Разделение по запятой в числах с десятичным разделителем-запятой приводит к разбиению числа — перед разделением столбец следует перевести в текстовый формат.
- При использовании «Текст по столбцам» с форматом «Фиксированная ширина» в кодировке Windows-1251 русские буквы занимают 2 байта, что может сдвинуть границы столбцов.
¶Историческая справка
Инструмент «Текст по столбцам» присутствует в Excel с середины 1990-х годов (начиная с версии Excel 95/97) и с тех пор не изменился по принципу работы. Функция РАЗДЕЛИТЬТЕКСТ появилась в 2022 году в обновлении Microsoft 365.