Как разделить в экселе по столбцам содержимое ячейки
С помощью мастера распределения текста по столбцам текст, содержащийся в одной ячейке, можно разделить на несколько.
Проверьте, как это работает!
Выделите ячейку или столбец с текстом, который вы хотите разделить.
На вкладке Данные нажмите кнопку Текст по столбцам.
В мастере распределения текста по столбцам установите переключатель с разделителями и нажмите кнопку Далее.
Выберите разделители для своих данных. Например, запятую и пробел. Данные можно предварительно просмотреть в окне Образец разбора данных.
Нажмите кнопку Далее.
В поле Поместить в выберите место на листе, где должны отображаться разделенные данные.
Разделение текста в Microsoft Excel
Способ 1: Использование автоматического инструмента
В Excel есть автоматический инструмент, предназначенный для разделения текста по столбцам. Он не работает в автоматическом режиме, поэтому все действия придется выполнять вручную, предварительно выбирая диапазон обрабатываемых данных. Однако настройка является максимально простой и быстрой в реализации.
- С зажатой левой кнопкой мыши выделите все ячейки, текст которых хотите разделить на столбцы.
- После этого перейдите на вкладку «Данные» и нажмите кнопку «Текст по столбцам».
- Появится окно «Мастера разделения текста по столбцам», в котором нужно выбрать формат данных «с разделителями». Разделителем чаще всего выступает пробел, но если это другой знак препинания, понадобится указать его в следующем шаге.
Из этой инструкции можно сделать вывод, что использование такого инструмента оптимально в тех ситуациях, когда разделение необходимо выполнить всего один раз, обозначив для каждого слова новый столбец. Однако если в таблицу постоянно вносятся новые данные, все время разделять их таким образом будет не совсем удобно, поэтому в таких случаях предлагаем ознакомиться со следующим способом.
Способ 2: Создание формулы разделения текста
В Excel можно самостоятельно создать относительно сложную формулу, которая позволит рассчитать позиции слов в ячейке, найти пробелы и разделить каждое на отдельные столбцы. В качестве примера мы возьмем ячейку, состоящую из трех слов, разделенных пробелами. Для каждого из них понадобится своя формула, поэтому разделим способ на три этапа.
Шаг 1: Разделение первого слова
Формула для первого слова самая простая, поскольку придется отталкиваться только от одного пробела для определения правильной позиции. Рассмотрим каждый шаг ее создания, чтобы сформировалась полная картина того, зачем нужны определенные вычисления.
-
Для удобства создадим три новые столбца с подписями, куда будем добавлять разделенный текст. Вы можете сделать так же или пропустить этот момент.
Полностью созданная формула имеет вид =ЛЕВСИМВ(A1;ПОИСК(» «;A1)-1) , вы же можете создать ее по приведенной выше инструкции или вставить эту, если условия и разделитель подходят. Не забывайте заменить обрабатываемую ячейку.
Шаг 2: Разделение второго слова
Самое трудное — разделить второе слово, которым в нашем случае является имя. Связано это с тем, что оно с двух сторон окружено пробелами, поэтому придется учитывать их оба, создавая массивную формулу для правильного расчета позиции.
-
В этом случае основной формулой станет =ПСТР( — запишите ее в таком виде, а затем переходите к окну настройки аргументов.
Формула получилась большая, и не все пользователи понимают, как именно она работает. Дело в том, что для поиска строки пришлось использовать сразу несколько функций, определяющих начальные и конечные позиции пробелов, а затем от них отнимался один символ, чтобы в результате эти самые пробелы не отображались. В итоге формула такая: =ПСТР(A1;ПОИСК(» «;A1)+1;ПОИСК(» «;A1;ПОИСК(» «;A1)+1)-ПОИСК(» «;A1)-1) . Используйте ее в качестве примера, заменяя номер ячейки с текстом.
Шаг 3: Разделение третьего слова
Последний шаг нашей инструкции подразумевает разделение третьего слова, что выглядит примерно так же, как это происходило с первым, но общая формула немного меняется.
-
В пустой ячейке для расположения будущего текста напишите =ПРАВСИМВ( и перейдите к аргументам этой функции.
Разделить текст по столбцам в Excel
Приложение Excel позволяет разбивать текст в ячейках столбца на несколько подстрок и располагать их в отдельных колонках. При этом для разбивки используется специальный разделитель, который выбирается пользователем для каждого конкретного случая.
Для примера рассмотрим данные CSV файла. Каждая подстрока в нем разделена символом «точка с запятой» и помещена в двойные кавычки. Вставьте их в любое место Вашей рабочей книги:
Перед разбивкой данных требуется выделить нужный диапазон, а затем кликнуть по пиктограмме «Текст по столбцам» в области «Работа с данными» на ленте вкладки «Данные».
На экране будет отображено окно мастера распределения текста:
Весь процесс состоит из трех шагов.
На первом этапе требуется выбрать формат данных для разделения, а именно указать, содержит ли текст разделители, или каждый будущий столбец имеет свою фиксированною ширину, т.е. длину текста. Последний вариант рассматриваться не будет, так как он ничем не отличается от первого кроме следующего шага, где требуется самостоятельно выбрать на линейке ширину поля.
Выберите пункт «С разделителями» и нажмите кнопку «Далее».
Следующим действием выбирается символ-разделитель. Им может быть любой из знаков, включая буквы и цифры.
Обратите внимание на то, что если разделители идут подряд, то каждый из них образует новый столбец, т.е. 2 таких символа создают 1 пустой столбец, 3 – 2 и т.д. Чтобы избавиться от этого часто нужного действия достаточно поставить галочку на поле «Считать последовательные разделители одним».
Может возникнуть ситуация, когда подстрока для создания столбца содержит символ-разделитель, который таковым не является. В подобных случаях строки должны быть заключены в одинарные либо двойные кавычки (в нашем примере исходных данных это присутствует), а в окне мастера нужно выбрать, какой ограничитель строк применить.
Имейте в виду то, что символы-разделители и ограничители удаляются из содержимого разделенных данных.
На любом описанным выше этапе можно нажать кнопку «Готово» для предоставления возможности приложению Excel самостоятельно завершить разделение текста в ячейках столбца. Но если Вы хотите контролировать весь процесс, то продолжайте нажимать «Далее».
Завершающим шагом является назначение форматирования новым колонкам и указания места для их расположения.
Из возможных настроек предоставлено только несколько форматов (типов данных), а поместить распределенный текст можно только на текущем листе.
7 примеров использования функции «Текст по столбцам» в Excel
Эта функция крайне удивительна и может послужить вам в разных ситуациях.
Самое простое её использование — разделить текст по нескольким столбикам, но её также можно использовать и для других целей.
Сегодня, я продемонстрирую несколько вариантов использования функции «Текст по столбцам».
Как использовать функцию «Текст по столбцам»
Все очень просто — щелкните «Данные» -> «Текст по столбцам»
Далее откроется окно функции. Само выполнение состоит из 3 этапов. На каждом определяются некоторые параметры, которые индивидуальны в каждом случае, не переживайте, мы их рассмотрим.
Итак, давайте я покажу вам варианты использования.
Деление строки
Допустим, у нас есть такая табличка:
И, например, нам нужно разделить имя + фамилию и поместить их в разные столбцы.
Как это сделать?
- Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);
- На первом этапе выбираем «с разделителями», так как мы будем делить строку по пробелу;
- На втором этапе, собственно, указываем что пробел в нашем случае будет разделителем строки;
- И на третьем этапе указываем куда поместить данные;
- Подтверждаем.
- Таким образом, в нашем конкретном случае пробел являлся разделителем. Если же у вас будет строка не просто имя пробел фамилия, а будут, например, инициалы, нужно будет менять схему деления;
- Также не забывайте о том, что результат выполнения функции будет статическим, т.е. если в изначальных ячейках меняются значения — вам нужно будет заново использовать функцию.
Делим электронную почту на имя пользователя и домен
Итак, в этом случае, мы будем использовать другой разделитель.
Допустим, у нас есть следующая табличка:
Я просто придумал эти почты исходя из фильмов по супергероям ��
Разделителем, как вы могли догадаться, в этом случае будет знак «@».
Давайте разделим строки.
Как это сделать?
- Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);
- На первом этапе выбираем «с разделителями», так как мы будем делить строку по знаку «@»;
- На втором этапе, в опции «другой» укажите наш разделитель;
- И укажите куда поместить данные после обработки;
- Подтвердите.
Получаем главный адрес сайта из ссылки
Бывает такое, что из множества ссылок вам нужно «вытащить» только главные адреса сайтов.
Например, в случае https://yandex.ru/primer1 главный адрес будет yandex.ru.
Итак, допустим у нас есть следующая табличка:
Давайте разделим строки и получим главные адреса сайтов!
Как это сделать?
- Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);
- На первом этапе выбираем «с разделителями»;
- На втором этапе выберите «другой» и укажите «/»;
- Укажите куда нужно поместить данные;
- Подтвердите.
Далее можно очистить таблицу от ненужных столбцов и дубликатов.
Важная информация: в случае, если ваши ссылки не имеют https:// в начале — все будет еще проще, вы получите главный адрес сайта в первом столбике.
Приведение дат к общему формату
Часто бывает так, что при импорте дат из разных источников они имеют разный формат.
Но что делать если этих данных очень много, а необходимо сделать так, чтобы все даты были в одном, «общем», формате?
Допустим, у нас есть такая табличка:
Давайте приведем все даты к одному формату!
Как это сделать?
- Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);
- На первом этапе выбираем «с разделителями»;
- На втором этапе уберите все галочки;
- На третьем этапе выберите опцию «дата — ДМГ» и укажите куда поместить обработанные данные;
- Подтвердите.
Готово! Таким образом даты были отформатированы.
Смена формата с текстового на числовой
Бывают случаи, когда вы получаете от кого-то табличку, но не можете быстро произвести расчеты, так как некоторые числа в ней имеют текстовый формат.
Такое может быть по нескольким причинам:
- Перед числом есть знак “ ‘ “;
- Эти числа выведны благодаря некоторым функциям, которые присваивают им текстовый формат.
Допустим, у нас есть такая табличка:
Давайте переведем их в формат «Числовой».
Как это сделать?
- Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);
- На первом этапе выбираем «с разделителями»;
- На втором этапе уберите все галочки;
- нужная опция «общий»;
- Подтвердите.
Итак, теперь все числа автоматически отформатируются как «общий» формат и их можно использовать при вычислениях.
Убираем несколько первых символов значения каждой ячейки
При работе в Excel вы можете столкнуться и с такой задачей.
Допустим, у нас есть такая табличка:
Давайте удалим первые пять букв с начала каждой строки.
Как это сделать?
- Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);
- На первом этапе выбираем «фиксированной ширины»;
- На втором этапе — поставьте указатель на нужное количество символов (в нашем случае пять);
- Укажите куда нужно поместить данные;
- Подтвердите.
Важная информация: можно использовать сразу несколько указателей, в таком случае строка будет разделена на большее количество частей.
Преобразуем числа со знаком минус на конце в отрицательные числа
Такое встречается довольно редко, но все равно будет полезно знать, что делать в такой ситуации.
Допустим, у нас есть такая табличка:
Давайте обработаем эти числа.
Как это сделать?
- Выделите диапазон ячеек и откройте окно функции («Данные» -> «Текст по столбцам»);
- На первом этапе выбираем «с разделителями»;
- На втором этапе уберите все галочки;
- На третьем этапе щелкните на «Подробнее…»;
- В открывшемся окне поставьте галочку на опции указанной на картинке ниже;
- Укажите куда поместить обработанные данные;
- Подтвердите.
Готово, теперь числа будут отрицательными. Можете использовать их в формулах.