Текст по столбцам
Как разделить текст ячейки Excel по разным столбцам, используя числовую закономерность расположения знаков или символ-разделитель





Как разделить текст по разным столбцам (колонкам), если в исходном документе разнородные фрагменты текста записаны в одной ячейке?

Конспекты

Курсы

Практикум


Скачать файл
Ссылка на курс
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

Как разделить текст ячейки Excel по разным столбцам, используя числовую закономерность расположения знаков или символ-разделитель

Как разделить текст по разным столбцам (колонкам), если в исходном документе разнородные фрагменты текста записаны в одной ячейке? Разъединить текст в ячейке можно либо по количественной закономерности символов (известно число знаков для каждого столбца), либо используя некий символ-разделитель. Команда Текст по столбцам способна решить оба вопроса.

ФАЙЛЫ ЭТОГО УРОКА по Excel МОЖНО СКАЧАТЬ ВНИЗУ СТРАНИЦЫ

 

Иногда в файле встречается ячейка, в которую записаны данные разного типа, но на самом деле было бы логично и удобно видеть эти данные в разных столбцах. Поделить по колонкам текст одной ячейки можно без проблем – достаточно знать способы входа и выхода из режима редактирования ячейки, выделение фрагмента текста и копирование-вставку.
Но что делать, если таких ячеек много, целый список? Правильно – использовать команду Excel, которая умеет автоматически распределять текст по столбцам на основании количественной закономерности символов либо по символу-разделителю. Выбрать сценарий можно в диалоговом окне Данные – Работа с данными – Текст по столбцам:

Рисунок 1

ФИКСИРОВАННАЯ ШИРИНАВыделить все ячейки, текст которых подлежит разбору.
В файле [Text To Columns 1 Passport RF.xlsx] группу ячеек A3:A51 можно выделить с помощью горячих клавиш – щелкнуть мышкой по A3, далее нажать и удерживать клавиши CTRL+SHIFT и нажать на клавиатуре стрелку вниз.

Щёлкнуть на ленте по кнопке Текст по столбцам и в появившемся диалоговом окне установить переключатель фиксированной ширины. Нажать Далее.

Рисунок 2

На следующем шаге в нижней половине окна щёлкнуть в цену деления линейки, соответствующее тому количеству знаков, которое оставляем в текущем столбце – следующие знаки программа перенесёт в соседний правый столбец листа. Нажать Далее.

Рисунок 3

Появившаяся черная линия-стрелка показывает будущую границу столбца. Эту линию можно перетаскивать мышкой, двойным щелчком – удалить (или перетащить в нулевое положение), можно поставить несколько линий, сколько требуется.

Можно было и на предыдущем шаге нажать кнопку Готово, но так как в основе разбора цифровая последовательность, то необходимо дойти до последнего шага, чтобы сохранить нули, ведь если они окажутся начальными знаками – программа их сбросит.
На последнем этапе в нижней части окна щёлкнуть по первой колонке и выбрать в верхней части переключатель текстовый, потом в нижней части окна щёлкнуть по второй колонке и снова выбрать в верхней части окошка переключатель текстовый. И так со всеми образующимся столбцами. Нажать Готово.

Рисунок 4

Результат разбора показан на рисунке ниже. Если Вам не нравятся зелёные (у меня – серые) уголочки в уголках ячеек (программа всего лишь напоминает, что числа сохранены как текст), увеличьте область выделения SHIFT + стрелка вправо, нажмите на значок жёлтого ромба с восклицательным знаком и выберите пункт Пропустить ошибку. Готово.  

Рисунок 5

Для закрепления материала в файле [Text To Columns 2 Passport Abroad.xlsx] выполните разбор знаков, чтобы в столбце Серия оказалось 2 знака, а остальные – в Номер.
Чтобы исходный список остался неизменённым, на последнем шаге в поле Поместить в укажите ячейку B2 (знаки долларов программа добавит сама, пусть будут) и именно с этой ячейки начиная Excel выведет разобранный список данных.

Рисунок 6

Если полный разбор текста не требуется, но есть необходимость вытащить из ячейки лишь опредлённый фрагмент текста, это можно сделать с помощью простых текстовых функций. Подробнее о них читайте в этом уроке.

 

ТЕКСТ ПО СТОЛБЦАМ - С РАЗДЕЛИТЕЛЯМИ

Выделить все ячейки, текст которых подлежит разбору.
В файле [Text To Columns 3 Article.xlsx] группу ячеек B2:B11 можно выделить с помощью горячих клавиш – щелкнуть мышкой по B2, далее нажать и удерживать клавиши CTRL+SHIFT и нажать на клавиатуре стрелку вниз.

На ленте на вкладке Данные щёлкнуть по кнопке Текст по столбцам, расположенной чуть правее центра, и в появившемся диалоговом окне установить переключатель с разделителями. Нажать Далее.

Рисунок 7

В верхней половине окна поставить флажок выбранным символам-разделителям, при необходимости поставить флажок другой и в белое поле справа вписать один знак. Нижняя часть окна будет демонстрировать образец разбора данных.
Так как в исходном списке перед последней частью текста указано три тире подряд, то будет полезно исключить лишние пустые столбцы с помощью флажка Считать последовательные разделители одним. Жмём Далее.

Рисунок 8

В поле Поместить в указать щелчком ячейку C2 и нажать Готово.

Рисунок 9

 Чтобы убедиться, все ли ячейки разобраны правильно, проверьте последний правый столбец из созданных и следующий за ним пустой.

Для этого сначала выбираем ячейку F2 и нажимаем CTRL + стрелка вниз – в результате этого действия программа Excel выберет последнюю заполненную ячейку перед первой пустой, встретившейся на пути. Это может означать конец нашего списка и тогда разбор текста считаем правильным, либо строку, где в исходном списке символ-разделитель был пропущен. В последнем случае придётся исправлять данные вручную. Excel работает безукоризненно, пока соблюдаются закономерности. Если они нарушены – не избежать ручной работы.

Теперь выбираем ячейку G2 и также нажимаем CTRL + стрелка вниз – в результате этого действия программа Excel выберет первую заполненную ячейку, встретившуюся на пути, и это будет означать, что не все символы нужно было учитывать в качестве разделителей. В примере тире являлся частью номера 981-25. Исправлять последствия нужно будет также вручную.
Если при нажатии CTRL + стрелка вниз мы оказались у последней строки листа, то нас можно поздравить – ошибок разбора текста нет :) Возвращаемся наверх нажатием CTRL + стрелка вверх.

 

В файле [Text To Columns 4 Visa.xlsx] самостоятельно выполните разбор текста по столбцам в два этапа: основной список по тире, а далее получившийся список цен расценок с текстом – по пробелу. Лишние пустые столбцы удалите. Должно получиться так:

Рисунок 10

Скорее всего, на первом этапе программа не среагирует на тире, потому как в основном списке – длинное тире и ввести его вручную при разборе текста не получится.
Прежде чем идти в окно Текст по столбцам, скопируйте этот тире из ячейки, выйдите из режима редактирования нажатием на клавишу ESC, а уж потом выделяйте разбираемые ячейки и выполняйте все шаги этой команды.
Будьте внимательны – в строке формул нужно выделить строго один знак! Если замечаете, что прихватывается пробел, попробуйте поставить курсор перед знаком тире (если нужно, перемещайте курсор стрелками клавиатуры вправо и влево), далее жмите SHIFT + стрелка вправо, потом копируйте CTRL + C, и в верхнем левом углу клавиатуры ESC для выхода из режима редактирования ячейки.

Рисунок 11

В файле [Text To Columns 5 Address.xlsx] попробуйте разобрать список адресов из колонки C, используя запятую как символ-разделитель. Наша цель – расположить в отдельных столбца названия городов, улиц, номера домов и т.п.
На этом примере легко убедиться, что программа Excel не всемогуща :) В исходном списке перепутаны местами части адреса, программа безупречно сработает и разберёт текст, ориентируясь на запятую, но менять порядок расположения данных не будет. Что делать? В данном примере штатными средствами Excel уже ничего не исправить. А в жизни – хорошо продумывать свои таблицы, чтобы не иметь проблем при их обработке.

Похожие материалы

Примеры использования функции ЕСЛИ
ЕСЛИ логическая функция
Как выполнить обработку ошибок с помощью ЕСЛИОШИБКА. Как ЕСЛИ выполнить проверку данных по нескольким условиям - И, ИЛИ, вложенные ЕСЛИ

Условное форматирование строк по формуле
Условное форматирование. По формуле.
Настройка правил условного форматирования с использованием формулы: выделение столбцов (проверка текста в шапке таблицы), обработка пустот, выделение повторов.

Оформление больших таблиц. Умная строка итогов и автоматическая нумерация. Удаление пустых строк. Быстрый ввод формул
Форматировать как таблицу - что за команда
На вкладке Главная есть кнопка Форматировать как таблицу. Как это работает? Какие задачи помогает решить данная команда и как отказаться от полосатого стиля, если он неудобен.

Пользовательские имена ячеек и диапазонов
Имена ячеек
Поиск влияющих и зависимых ячеек. Пользовательские имена - способы создания имен, использование в новых формулах, подстановка имен в старые формулы.

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



Оставляя заявку, вы соглашаетесь на обработку ваших персональных данных.