Текст по столбцам
Как разделить текст ячейки 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 уже ничего не исправить. А в жизни – хорошо продумывать свои таблицы, чтобы не иметь проблем при их обработке.

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

Интерфейс и основные понятия
Основные понятия
Вводный урок Excel, чтобы разобраться, как правильно называются элементы интерфейса программы. Это займёт немного времени, но дальнейшие материалы будут намного понятнее.

ПЛТ для расчета аннуитетного платежа
ПЛТ для расчета аннуитетного платежа
Как составить график аннуитетных платежей. Встроенная функция Excel - ПЛТ - работает по стандартной формуле, которая используется всеми финансовыми структурами.

Правила и закономерности, по которым работает Excel
Общие принципы, лежащие в основе любого инструмента программы Excel
Как понять программу Excel? Это несложно, список правил и настроек приведён в этом уроке.

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

Заказчики

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

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

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

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



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