Текст в столбцы, мгновенное заполнение и формулы

Как разделить ячейку в Excel? С помощью функции «Текст в столбцы», «Быстрая заливка», формул или инструмента «Разделить текст». В этом руководстве описаны все варианты, которые помогут вам выбрать технику, наиболее подходящую для вашей конкретной задачи.

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

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

Функция «Текст в столбцы» очень удобна, когда вам нужно разделить содержимое ячейки на две или более ячеек. Он позволяет разделять текстовые строки с помощью определенного разделителя, такого как запятая, точка с запятой или пробел, а также разбивать строки фиксированной длины. Давайте посмотрим, как работает каждый сценарий.

Как разделить ячейки в Excel разделителем

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

Мы хотим разделить данные в одной ячейке на несколько ячеек, таких как Имя, Фамилия, Страна, Дата прибытия а также Статус. Чтобы это сделать, выполните следующие действия:

1. Если вы хотите поместить результаты в середину таблицы, начните с добавления новых столбцов, чтобы избежать перезаписи существующих данных. В этом примере мы вставили 3 новых столбца, как показано на снимке экрана ниже:
   Если у вас нет данных рядом со столбцом, который вы хотите отделить, пропустите этот шаг.
2. Выберите ячейки, которые вы хотите разделить, перейдите к Данные вкладка > Инструменты данных группу и нажмите кнопку «Текст в столбцы».
   
3. На первом шаге Преобразование текста в столбцы мастер, вы выбираете, как разделить ячейки – по разделителю или ширине. В нашем случае содержимое ячейки разделено пробелами и запятыми, поэтому мы выбираем Delimited и нажимаем Следующий.
   
4. На следующем шаге вы указываете разделители и, при необходимости, квалификатор текста. Вы можете выбрать один или несколько предопределенных разделителей, а также ввести свой собственный в поле Другой коробка. В этом примере мы выбираем Пространство а также Запятая:
   

   Советы:

   • Рассматривать последовательные разделители как один. Обязательно выберите этот параметр, если ваши данные могут содержать два или более разделителя подряд, например, когда между словами есть несколько последовательных пробелов или данные разделены запятой и пробелом, например «Смит, Джон».
   • Указание квалификатора текста. Используйте этот параметр, если некоторый текст заключен в одинарные или двойные кавычки, и вы хотите, чтобы такие части текста были неразделимы. Например, если вы выберете запятую (,) в качестве разделителя и кавычки (“) в качестве квалификатора текста, тогда любые слова, заключенные в двойные кавычки, например “Калифорния, США”будет помещаться в одну ячейку как Калифорния, США. Если вы выберете {никто} в качестве квалификатора текста, то “Калифорния будет распределен в одну ячейку (вместе с открывающей кавычкой) и США” в другой (вместе с закрывающим знаком).
   • Предварительный просмотр данных. Прежде чем нажать на Следующий кнопку, имеет смысл пролистать Предварительный просмотр данных раздел, чтобы убедиться, что Excel правильно разделил все содержимое ячеек.
5. Вам осталось сделать еще две вещи — выбрать формат данных и указать, куда вы хотите вставить полученные значения:
   • Формат данных. По умолчанию Общий Формат установлен для всех столбцов, что хорошо работает в большинстве случаев. В нашем примере нам понадобится Данные формат даты прибытия. Чтобы изменить формат данных для определенного столбца, щелкните этот столбец в Предварительный просмотр данных чтобы выбрать его, а затем выберите один из форматов в разделе Формат данных столбца (см. скриншот ниже).
   • Назначения. Чтобы сообщить Excel, куда вы хотите вывести разделенные данные, щелкните значок Свернуть диалоговое окно значок сразу после Назначения поле и выберите самая верхняя левая ячейка целевого диапазона или введите ссылку на ячейку прямо в поле. Пожалуйста, будьте очень осторожны с этой опцией и убедитесь, что есть достаточно пустых столбцов прямо до целевой ячейки.
     

   Заметки:

   • Если вы не хотите импортировать какой-либо столбец, отображаемый в предварительном просмотре данных, выберите этот столбец и установите флажок Не импортировать столбец (пропустить) переключатель под Формат данных столбца.
   • Невозможно импортировать разделенные данные в другую электронную таблицу или книгу. Если вы попытаетесь сделать это, вы получите сообщение об ошибке недопустимого адресата.
6. Наконец, нажмите на Заканчивать кнопка и готово! Как показано на скриншоте ниже, Excel идеально разместил содержимое одной ячейки в несколько ячеек:
   

Как разделить текст фиксированной ширины

В этом разделе объясняется, как разделить ячейку в Excel на основе указанного вами количества символов. Чтобы упростить понимание, рассмотрим следующий пример.

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

Поскольку все идентификаторы продуктов содержат 9 символов, Фиксированная ширина Вариант идеально подходит для работы:

1. Начать Преобразование текста в столбцы мастер, как описано в приведенном выше примере. На первом шаге мастера выберите Фиксированная ширина и нажмите Следующий.
   
2. Задайте ширину каждого столбца с помощью Предварительный просмотр данных раздел. Как показано на снимке экрана ниже, вертикальная линия представляет собой разрыв столбца, и для создания новой строки разрыва вы просто щелкаете в нужном месте (в нашем случае 9 символов):
   Чтобы удалить разрыв, дважды щелкните строку; чтобы переместить разрыв в другую позицию, просто перетащите линию мышью.
3. На следующем шаге выберите формат данных и место назначения для разделенных ячеек точно так же, как мы делали это в предыдущем примере, и нажмите кнопку Заканчивать кнопку, чтобы завершить разделение.

Как разделить ячейки Excel с помощью Flash Fill

Начиная с Excel 2013, вы можете использовать функцию мгновенного заполнения, которая может не только автоматически заполнять ячейки данными, но и разделять содержимое ячеек.

Давайте возьмем столбец данных из нашего первого примера и посмотрим, как мгновенное заполнение Excel может помочь нам разделить ячейку пополам:

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

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

Какую бы разнообразную информацию ни содержали ваши ячейки, формула для разделения ячейки в Excel сводится к поиску положения разделителя (запятая, пробел и т. д.) и извлечению подстроки до, после или между разделителями. Как правило, вы должны использовать функции SEARCH или FIND для определения местоположения разделителя и одну из функций Text (LEFT, RIGHT или MID) для получения подстроки.

Например, вы можете использовать следующие формулы для разделения данных в ячейке A2, разделенных запятой и пробелом (см. снимок экрана ниже):

Чтобы извлечь имя в B2:

=ЛЕВО(A2, ПОИСК(“,”,A2)-1)

Здесь функция ПОИСК определяет позицию запятой в ячейке А2, и вы вычитаете из результата 1, потому что сама запятая в выводе не ожидается. Функция LEFT извлекает это количество символов из начала строки.

Чтобы извлечь страну в C2:

=ВПРАВО(A2, ДЛСТР(A2)-ПОИСК(“,”, A2)-1)

Здесь функция ДЛСТР вычисляет общую длину строки, из которой вы вычитаете положение запятой, возвращаемое ПОИСК. Кроме того, вы вычитаете пробел (-1). Разница относится ко второму аргументу RIGHT, поэтому он извлекает столько символов из конца строки.

Результат будет выглядеть следующим образом:

Если ваш разделитель представляет собой запятую с пробелом или без него, вы можете использовать следующую формулу для извлечения подстроки после него (где 1000 — максимальное количество символов для извлечения):

=ОТРЕЗАТЬ(СРЕДН(A2, ПОИСК(“,”, A2)+1, 1000))

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

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

• TEXTSPLIT – разделять строки по любому указанному вами разделителю.
• TEXTBEFORE — извлечь текст перед определенным символом или подстрокой.
• TEXTAFTER – извлечь текст после определенного символа или слова.

Дополнительные примеры формул для разделения ячеек в Excel можно найти на следующих ресурсах:

Разделить ячейки с помощью функции «Разделить текст»

Теперь, когда вы знакомы со встроенными функциями, позвольте мне показать вам альтернативный способ разделения ячеек в Excel. Я имею в виду инструмент «Разделить текст», входящий в состав Ultimate Suite for Excel. Он может выполнять следующие операции:

• Разделить ячейку по символу
• Разделить ячейку по строке
• Разделить ячейку по маске (шаблон)

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

1. Выберите ячейки, которые вы хотите разделить, и щелкните значок «Разделить текст» на Данные об аблебитах вкладка, в Текст группа.
   
2. На панели надстройки настройте следующие параметры:
   • Выберите запятую и пробел в качестве разделителей.
   • Установите флажок Рассматривать последовательные разделители как один флажок.
   • Выберите Разбить на столбцы.
   • Нажмите кнопку Разделить.

   

Сделанный! Четыре новых столбца с разделенными данными вставляются между исходными столбцами, и вам нужно только дать этим столбцам соответствующие имена:

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

Если вам интересно увидеть инструменты «Разделить текст» и «Разделить имена» в действии, воспользуйтесь ссылкой для скачивания ниже. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Доступные загрузки

Ultimate Suite 14-дневная полнофункциональная версия (файл .exe)

Вас также могут заинтересовать