Разделение текста по столбцам в Excel: мастер, мгновенное заполнение и формулы
Выгрузка из программы приходит одной колонкой: «Иванов Иван Иванович», «Москва, ул. Ленина, 5», «товар; 12 шт; 340». Сортировать и считать такое неудобно. Excel умеет разбивать текст на части, но у каждого способа есть свои последствия. Разберём три подхода и типичные ошибки, из-за которых данные теряются.
Что именно нужно разделить
Сначала посмотрите на образец данных.
Определите, чем отделены части: запятой, точкой с запятой, пробелом или табуляцией.
Проверьте, нет ли внутри значений тех же символов.
От этого зависит выбор способа.
Способ первый: мастер «Текст по столбцам»
Выделите столбец с данными.
Откройте вкладку «Данные».
Нажмите «Текст по столбцам».
Откроется пошаговый мастер из трёх экранов.
Шаг с разделителями
Выберите формат «с разделителями».
Нажмите «Далее».
Отметьте нужный символ: запятую, точку с запятой, пробел или другой.
В окне предпросмотра сразу видно, как разложатся данные.
Шаг с шириной
Второй формат — фиксированная ширина.
Он подходит, когда части имеют одинаковую длину.
Границы столбцов ставятся вручную мышью по линейке.
Для текста переменной длины этот способ неудобен.
Куда попадёт результат
В мастере есть поле назначения.
По умолчанию данные заменят исходный столбец и заполнят соседние справа.
Если там есть информация, она будет перезаписана.
Поэтому перед запуском вставьте пустые столбцы или укажите другое место.
Защита от потери данных
Скопируйте исходный столбец на отдельный лист.
Или сделайте копию файла перед преобразованием.
Отмена поможет, но только сразу.
После сохранения и закрытия файла вернуть данные будет нечем.
Формат данных в мастере
На последнем шаге можно задать формат каждого нового столбца.
Для кодов с ведущими нулями выберите текстовый формат.
Иначе «007» превратится в «7».
Для дат укажите правильный порядок день-месяц-год.
Способ второй: мгновенное заполнение
Введите в соседнем столбце нужное значение для первой строки.
Перейдите на строку ниже и нажмите сочетание клавиш для заполнения.
Excel попробует угадать закономерность.
Проверьте несколько строк, прежде чем применять ко всей колонке.
Когда мгновенное заполнение удобно
Когда части не разделены одним и тем же символом.
Когда нужно вытащить только имя или только город.
Когда правило проще показать, чем описать.
Метод не обновляется при изменении исходных данных.
Способ третий: формулы
Функция для разделения по разделителю появилась в новых версиях Excel.
Она возвращает массив значений и растягивается вправо.
Доступна не во всех версиях и не в веб-варианте одинаково.
Проверьте, что ваша версия её поддерживает.
Преимущество формул
Результат обновляется при изменении источника.
Не нужно повторять мастер при каждой правке.
Формулы можно скопировать на весь диапазон.
Но они занимают соседние ячейки, которые должны быть пустыми.
Ошибка с переполнением
Если на пути массива есть данные, появится ошибка переполнения.
Освободите область справа.
Не пытайтесь «протолкнуть» формулу в занятые ячейки.
Это частая причина появления значков ошибки.
Нули и артикулы
Артикулы и коды часто содержат ведущие нули.
При импорте Excel может принять их за числа.
Мастер с текстовым форматом спасает ситуацию.
Проверяйте первые строки после преобразования.
Даты и региональные настройки
Разделитель в датах зависит от региональных параметров.
США используют месяц-день-год, Европа — день-месяц-год.
При разделении по точке будет путаница.
Указывайте формат вручную на последнем шаге.
Текст с кавычками
Если значения заключены в кавычки и содержат запятые внутри, простой мастер ошибается.
Проверьте предпросмотр.
При необходимости сначала замените внутренние разделители.
Или используйте импорт с настройкой текстового квалификатора.
Проверка результата
Сравните количество строк до и после.
Посмотрите, не появились ли пустые ячейки там, где их быть не должно.
Проверьте суммы и количество записей.
Убедитесь, что ни один столбец не потерялся.
Что делать, если разделитель не сработал
Убедитесь, что выбран именно тот символ.
Проверьте, нет ли лишних пробелов вокруг значений.
Иногда помогает предварительная замена двойных пробелов.
Для невидимых символов используйте функцию очистки.
Что делать с лишними пробелами
Перед разделением удалите двойные пробелы.
Функция поиска и замены справится за секунду.
Иначе в новых столбцах появятся пустые значения.
Проверьте крайние случаи: пробел в начале или в конце.
Разделение адресов
Адрес удобно делить по запятым.
Но внутри адреса тоже бывают запятые.
Сначала посмотрите на несколько примеров.
Если структура неоднородна, ручная правка неизбежна.
Разделение ФИО
Фамилия, имя и отчество обычно разделены пробелами.
Двойные фамилии через дефис останутся одним значением.
При разделении по пробелу дефис не мешает.
Проверьте строки с инициалами.
Слияние обратно
Для обратного действия используйте функцию объединения текста.
Она склеивает значения с выбранным разделителем.
Так можно проверить, что разделение было корректным.
Сравните результат с исходным столбцом.
Работа с большими выгрузками
Сначала обработайте первые сто строк.
Проверьте результат.
Затем применяйте ко всему диапазону.
Так ошибка не разойдётся по всей таблице.
Сохранение промежуточного результата
Сохраните файл после разделения.
Сделайте копию с датой в имени.
Если что-то пойдёт не так, вернётесь к рабочей версии.
Не полагайтесь только на отмену действия.
Оформление новых столбцов
Задайте ширину столбцов после разделения.
Проверьте, что числа выровнены по правому краю.
Текстовые значения — по левому.
Это облегчает визуальную проверку.
Когда нужен импорт, а не разделение
Если данные приходят из другого файла регулярно, настройте импорт.
Тогда разделение выполняется при загрузке.
Это надёжнее ручной операции каждый раз.
Для разовой задачи достаточно мастера.
Что запомнить
- Мастер «Текст по столбцам» перезаписывает соседние ячейки.
- Перед преобразованием сделайте копию столбца или файла.
- Ведущие нули сохраняются только в текстовом формате.
- Мгновенное заполнение не обновляется автоматически.
- Формулы удобны, когда данные меняются.
FAQ
Можно ли разделить данные без потери исходного столбца?
Да, если указать в мастере другое место назначения.
Работает ли мастер в Excel для интернета?
Часть функций там недоступна, проверяйте на своей версии.
Что делать с ошибкой переполнения?
Освободить ячейки справа от формулы.
Как разделить ФИО на три столбца?
Мастером по пробелу или мгновенным заполнением с примером.