Как разделить в экселе текст по столбцам. Разделение текста в Microsoft Excel

Учимся делить текст в ячейке excel по столбцам при помощи мастера

srv-spb.ru » Microsoft » Office » Excel

Всем привет! С вами на связи Александр Глебов. В данной нам мини статье я рассказываю про то, как поделить текст в ячейке excel по столбцам. Это чрезвычайно нужная функция excel которая может существенно облегчить жизнь при работе с однотипным текстом. Не буду лить воду, поехали…

Для начала желал бы кратко поведать, что имеется ввиду под понятием поделить текст в ячейке по столбцам. Представим у вас есть перечень фамилий, написанный в строку, а разделителем служит – ; (точка с запятой), и для вас чрезвычайно необходимо все фамилии разбить по столбцам. 

Для этого употребляется особый инструмент “Текст по столбцам”, который за несколько шагов разобьет строчку в отдельные ячейки (столбцы).

Как поделить текст в ячейке excel по столбцам по шагам

Итак, что такое разделения текста мы разобрались. Сейчас давайте поглядим, как фактически разбить текст по столбцам. Для этого выполним последующие шаги:

  1. Будем мыслить, что excel уже запущен, и подходящий текст вставлен в ячейку.
  2. Далее перебегаем на вкладку, там жмем клавишу “Текст по столбцам”.
  3. У вас раскроется мастер: “Мастер текстов (разбор) – шаг 1 из 3”, в котором нужно выбрать формат начальных данных. С разделителями либо фиксированная ширина. У нас есть разделитель, точка с запятой. Означает, нам нужно выбрать форма с разделителями. Жмем Далее.
  4. На втором шаге, для вас будет предложено выбрать разделитель. Так как у нас таковым является точка с запятой, ставим галку напротив нее. Ниже сходу видно будущий итог.  Нажимаем Далее.
  5. На 3-ем шаге для вас предлагается выбрать формат получаемых данных. Можно для каждого столбца выбрать собственный формат. Избираем подходящий для вас формат, в большинстве случаем подступает “Общий”. Жмем готово.

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

Если у вас остались вопросцы по тому, как разбить текст по столбцам в excel, то задавайте их в комментах. Буду рад помочь.

Загрузка…
Интересные статьи по теме:

Как разбить ячейки в Excel: «Текст по столбцам», «Мгновенное заполнение» и формулы

В данной статье Вы найдёте несколько методов, как разбить ячейки либо целые столбцы в Excel 2010 и 2013. Приведённые примеры и скриншоты иллюстрируют работу с инструментами «Текст по столбцам» и «Мгновенное заполнение», не считая этого Вы увидите выборку формул для разделения имён, текстовых и числовых значений. Этот урок поможет Для вас выбрать лучший способ разбиения данных в Excel.

Говоря в общем, необходимость разбить ячейки в Excel может появиться в 2-ух случаях: Во-1-х, при импорте инфы из какой-нибудь наружной базы данных либо с интернет-страницы. При таком импорте все записи копируются в один столбец, а необходимо, чтоб они были помещены в различных столбцах. Во-2-х, при разбиении уже имеющейся таблицы, чтоб получить возможность лучше настроить работу фильтра, сортировку либо для наиболее детализированного анализа.

Разбиваем ячейки в Excel при помощи инструмента «Текст по столбцам»

Инструмент «Текст по столбцам» вправду чрезвычайно комфортен, когда необходимо поделить данные из 1-го столбца по нескольким в Excel 2013, 2010, 2007 либо 2003.

«Текст по столбцам» дозволяет разбивать значения ячеек, отделённые разделителями, либо выделять данные фиксированной ширины (когда все значения содержат определённое количество символов). Давайте разглядим эти варианты подробнее:

Разбиваем текстовые данные с разделителями по столбцам в Excel

Предположим, есть перечень участников, приглашённых на конференцию либо какое-то другое мероприятие. На рисунке ниже видно, что в столбце Participant (Участник) перечислены имена участников, правительство и ожидаемая дата прибытия:

Читайте также  Как в outlook добавить учетную запись. Почта Outlook Windows и вторая учётная запись

Необходимо разбить этот текст на отдельные столбцы, чтоб таблица имела последующие данные (слева направо): First Name (Имя), Last Name (Фамилия), Country (Страна), Arrival Date (Ожидаемая дата прибытия) и Status (Статус).

  1. Если в таблице есть хотя бы один столбец справа от столбца, который нужно разбить, тогда первым делом сделайте новейшие пустые столбцы, в которые будут помещены приобретенные данные. Этот шаг нужен для того, чтоб результаты не были записаны поверх уже имеющихся данных.В нашем примере сходу опосля столбца Participant находится столбец Status, и мы собираемся добавить меж ними новейшие столбцы Last Name, Country и Arrival Date.Если кто-то запамятовал, я напомню стремительный метод вставить сходу несколько столбцов на лист Excel. Для этого выберите столбец Status, кликнув по его заголовку, и, удерживая нажатой левую клавишу мыши, протащите указатель на право, чтоб выделить необходимое количество столбцов (сколько желаете вставить). Потом кликните правой клавишей мыши по выделенной области и в контекстном меню выберите команду Insert (Вставить).

    Результат будет приблизительно таковым, что Вы видите на рисунке ниже (новые столбцы вставлены слева от выделенных столбцов):

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

  1. Выделите столбец, который требуется разбить. Потом откройте вкладку Data (Данные) > Data Tools (Работа с данными) > Text to Columns (Текст по столбцам).
  2. Откроется диалоговое окно Convert Text to Columns wizard (Мастер распределения текста по столбцам). На первом шаге профессионалы Вы выбираете формат данных. Так как записи разбиты пробелами и запятыми, мы избираем формат Delimited (С разделителями). Вариант Fixed width (Фиксированной ширины) будет рассмотрен чуток позднее. Ежели все готово, нажимайте Next (Далее), чтоб продолжить.
  3. На последующем шаге определяем разделители, которые содержатся в данных, и ограничитель строк.
    • Настраиваем разделители. Ежели данные разбиты одним либо несколькими разделителями, то необходимо выбрать все пригодные варианты в разделе Delimiters (Символом-разделителем является) либо ввести собственный вариант разделителя в поле Other (Другой).В нашем примере мы избираем Space (Пробел) и Comma (Запятая), а также ставим галочку напротив параметра Treat consecutive delimiters as one (Считать поочередные разделители одним). Этот параметр поможет избежать излишнего разбиения данных, к примеру, когда меж словами есть 2 либо наиболее поочередных пробела.
    • Настраиваем ограничитель строк. Этот параметр может пригодиться, ежели в столбце, который Вы разбиваете, содержатся какие-либо значения, заключённые в кавычки либо в апострофы, и Вы желаете, чтоб такие участки текста не разбивались, а рассматривались как цельные значения. К примеру, ежели Вы выберите в качестве разделителя запятую, а в качестве ограничителя строк – кавычки (“), тогда любые слова, заключённые в кавычки (например, “California, USA”), будут помещены в одну ячейку. Ежели же в качестве ограничителя строк установить значение None (Нет), тогда слово “California” будет помещено в один столбец, а “USA” – в другой.

    В нижней части диалогового окна находится область Data preview (Образец разбора данных). До этого чем надавить Next (Далее) будет разумным пролистать это поле и убедиться, что Excel верно распределил все данные по столбцам.

  4. Осталось сделать всего две вещи – выбрать формат данных и указать, куда поместить разделённые ячейки.В разделе Column data format (Формат данных столбца) Вы сможете выбрать формат данных раздельно для каждого столбца, в которые будут помещены разделённые данные. По умолчанию для всех столбцов задан формат General (Общий). Мы оставим его без конфигураций для первых трёх столбцов, а для четвёртого столбца установим формат Data (Дата), что разумно, ведь в этот столбец попадут даты прибытия.Чтобы поменять формат данных для каждого определенного столбца, выделите его, кликнув по нему в области Data preview (Образец разбора данных), а потом установите хотимый формат в разделе Column data format (Формат данных столбца).

    На этом же шаге профессионалы Вы сможете выбрать, в какой столбец поместить разделённые данные. Для этого кликните по иконке выбора спектра (в определениях Microsoft эта иконка именуется Свернуть диалоговое окно) справа от поля Destination (Поместить в) и выберите последний левый столбец из тех, в которые Вы желаете поместить разделённые данные. К огорчению, нереально импортировать разделённые данные на иной лист либо в другую рабочую книжку, попытка сделать это приведёт к сообщению о ошибке выбора конечной ссылки.

Читайте также  Не работает браслет xiaomi mi band 2. Что делать, если Mi Band 2 не включается — способы решения проблемы

Совет: Ежели Вы не желаете импортировать некий столбец (столбцы), который показан в области Data preview (Образец разбора данных), то выделите его и выберите вариант Do not import column (Пропустить столбец) в разделе Column data format (Формат данных столбца).

  1. Нажмите Finish (Готово)!

Разбиваем текст фиксированной ширины по нескольким столбцам

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

К примеру, есть перечень продуктов с ID и наименованием, при этом идентификатор продукта – это 9 знаков, которые стоят перед наименованием этого товара:

Вот что Для вас необходимо сделать, чтоб разбить таковой столбец на два:

  1. Запустите инструмент Text to Columns (Текст по столбцам), как мы это делали в прошлом примере. На первом шаге профессионалы выберите параметр Fixed width (Фиксированной ширины) и нажмите Next (Далее).
  2. В разделе Data preview (Образец разбора данных) настройте ширину столбцов. Как видно на рисунке ниже, край столбца символизирует вертикальная линия, и чтоб задать край последующего столбца, просто кликните в подходящем месте. Двойной щелчок по вертикальной полосы удалит край столбца, а ежели Для вас необходимо переместить границу столбца в другое место, просто перетащите вертикальную линию мышью. На самом деле, все эти аннотации тщательно расписаны в верхней части диалогового окна Так как каждый ID продукта содержит 9 знаков, устанавливаем линию границы столбца на это значение, как показано на рисунке выше.
  3. На последующем шаге выберите формат данных и укажите ячейки, куда поместить итог, как это было изготовлено в прошлом примере, а потом нажмите Finish (Готово).

Разбиваем объединённые ячейки в Excel

Если Вы объединили несколько ячеек на листе Excel и сейчас желаете вновь разбить их по отдельным столбцам, откройте вкладку Home (Главная) и в группе команд Alignment (Выравнивание) нажмите небольшую чёрную стрелку рядом с клавишей Merge & Center (Объединить и поместить в центре). Дальше из выпадающего перечня выберите Unmerge Cells (Отменить объединение ячеек).

Таким образом объединение ячеек будет отменено, но наслаждение от результата будет испорчено тем, что все данные останутся в левом столбце. Думаю, Вы додумались, что необходимо опять применять функцию Text to Columns (Текст по столбцам), чтоб разбить данные из 1-го столбца на два либо наиболее столбцов.

Разделяем данные на несколько столбцов в Excel 2013 при помощи моментального заполнения

Если Вы уже обновились до Excel 2013, то сможете пользоваться преимуществами новейшего инструмента «Мгновенное заполнение» и вынудить Excel автоматом заполнять (в нашем случае – разбивать) данные, при обнаружении определенной закономерности.

Если Вы ещё не знакомы с данной для нас функцией, я попробую коротко разъяснить её сущность. Этот инструмент анализирует данные, которые Вы вводите на рабочий лист, и пробует узнать, откуда они взялись и существует ли в их какая-либо закономерность. Как лишь «Мгновенное заполнение» распознает Ваши деяния и вычислит закономерность, Excel предложит вариант, и последовательность записей в новеньком столбце покажется практически за мгновение.

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

Первым делом, удостоверьтесь, что инструмент «Мгновенное заполнение» включен. Вы найдёте этот параметр на вкладке File (Файл) > Options (Параметры) > Advanced (Дополнительно) > Automatically Flash Fill (Автоматически делать мгновенное заполнение).

Теперь давайте поглядим, как можно автоматом разбить данные по ячейкам. Итак, Вы включили инструмент «Мгновенное заполнение», и начинаете вводить с клавиатуры данные, которые необходимо поместить в отдельные ячейки. По мере ввода Excel будет пробовать распознать шаблон в вводимых значениях, и как лишь он его распознает, данные автоматом будут вставлены в другие ячейки. Чтоб осознать, как это работает, поглядите на набросок ниже:

Читайте также  Как выключить айфон с помощью кнопок. Перезапуск iPhone

Как видите, я ввёл лишь пару имён в столбец B, и «Мгновенное заполнение» автоматом заполнило другие ячейки именами из столбца A. Ежели вы довольны результатом, просто нажмите Enter, и весь столбец будет заполнен именами. Чрезвычайно умный инструмент, не правда ли?

Если «Мгновенное заполнение» включено, но не дает никаких вариантов, которые соответствуют определённому шаблону, Вы сможете запустить этот инструмент вручную на вкладке Data (Данные) > Flash Fill (Мгновенное заполнение) либо нажав сочетание кнопок Ctrl+E.

Как в Excel разбивать ячейки при помощи формул

Существуют формулы, которые могут быть чрезвычайно полезны, когда возникает необходимость разбить ячейки либо столбцы с данными в Excel. На самом деле, последующих 6 функций будет довольно в большинстве случаев – LEFT (ЛЕВСИМВ), MID (ПСТР), RIGHT (ПРАВСИМВ), FIND (НАЙТИ), SEARCH (ПОИСК) и LEN (ДЛСТР). Дальше в этом разделе я коротко объясню назначение каждой из этих функций и приведу примеры, которые Вы можете применять в собственных книжках Excel.

Пример 1

Самая распространённая ситуация, когда могут пригодится эти формулы – это необходимость поделить имена из 1-го столбца по нескольким. На рисунке ниже показано, какого результата мы пытаемся достичь:

Вы просто можете разбить такие имена на два столбца при помощи последующих формул:

  • Извлекаем имя (столбец First name):

  • Извлекаем фамилию (столбец Last name):

Для тех, кому любопытно, что означают эти формулы, я попробую разъяснить наиболее подробно.

SEARCH (ПОИСК) либо FIND (НАЙТИ) – это полностью схожие функции, которые выполняют поиск позиции определенной текстовой строчки в данной ячейке. Синтаксис формулы:

В качестве аргументов Вы должны указать: что необходимо отыскать, где необходимо находить, а также позицию знака, с которого следует начать поиск. В нашем примере SEARCH(” “,A2,1) либо ПОИСК(” “;A2;1) говорит о том, что мы желаем отыскать знак пробела в ячейке A2 и начнём поиск с первого символа.

Замечание: Ежели поиск начинается с первого знака, Вы сможете вообщем пропустить аргумент start_num (нач_позиция) в формуле и упростить её до такового вида:

LEFT (ЛЕВСИМВ) и RIGHT (ПРАВСИМВ) – возвращает левую либо правую часть текста из данной ячейки соответственно. Синтаксис формулы:

В качестве аргументов указываем: какой текст взять и сколько знаков извлечь. В последующем примере формула будет извлекать левую часть текста из ячейки A2 вплоть до позиции первого отысканного пробела.

LEN (ДЛСТР) – считает длину строчки, то есть количество знаков в данной ячейке. Синтаксис формулы:

Следующая формула считает количество знаков в ячейке A2:

Если имена в Вашей таблице содержат отчества либо суффиксы, то потребуются мало наиболее сложные формулы с внедрением функции MID (ПСТР).

Пример 2

Вот такие формулы необходимо применять, когда имена, которые требуется разбить, содержат отчество либо лишь один инициал отчества посередине.

A B C D
1 Полное имя Имя Отчество Фамилия
2 Sally K. Brooke Sally K. Brooke
  • Извлекаем имя:

  • Извлекаем отчество:

  • Извлекаем фамилию:

Функция MID (ПСТР) – извлекает часть текстовой строчки (то есть данное количество символов). Синтаксис:

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

Пример 3

Вы сможете употреблять подобные формулы, чтоб разбить имена с суффиксами в конце:

A B C D
1 Полное имя Имя Фамилия Суффикс
2 Robert Furlan Jr. Robert Furlan Jr.
  • Извлекаем имя:

  • Извлекаем фамилию:

  • Извлекаем суффикс:

Пример 4

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

A B C D
1 Полное имя Имя Отчество Фамилия
2 White, David Mark David Mark White
  • Извлекаем имя:

  • Извлекаем отчество:

  • Извлекаем фамилию:

Пример 5

Как Вы осознаете, эти формулы работают не лишь для разделения имён в Excel. Вы сможете употреблять их для разбиения всех данных из 1-го столбца по нескольким. К примеру, последующие формулы Вы сможете употреблять, чтоб разбить текстовые данные, разделённые запятыми:

A B C D
1 Полное обозначение Штат Аббревиатура Столица
2 Alabama, AL, Montgomery Alabama AL Montgomery
  • Извлекаем заглавие штата:

  • Извлекаем аббревиатуру штата:

  • Извлекаем столицу штата:

А вот пример настоящих данных из Excel 2010. Данные из первого столбца разбиты на три отдельных столбца:

Оцените качество статьи. Нам принципиально ваше мнение:

Оставьте комментарий