В Excel существуют различные причины, по которым вам может понадобиться разделить данные ячеек. Например, необработанные данные могут содержать несколько фрагментов информации, объединенных в одну ячейку, например полные имена или адреса. Разделение этих ячеек позволяет разделять различные типы информации, упрощая очистку и анализ данных. Эта статья послужит вашим исчерпывающим руководством, показывающим различные способы разделения ячеек на строки или столбцы на основе определенных разделителей.

Разделить ячейки в Excel на несколько столбцов

• С помощью мастера «Текст в столбец»
• Легко с Kutools
• С быстрой заливкой (Excel 2013 и более поздние версии)
• С формулами

Разделить ячейки в Excel на несколько строк

• С функцией ТЕКСПЛИТ (Excel 365)
• Легко с Kutools
• С кодом VBA
• Доступно Power Query (Excel 2016 и более поздние версии)

Видео

Разделить ячейки в Excel на несколько столбцов

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

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

Чтобы разделить ячейки на несколько столбцов на основе определенного разделителя, обычно используется метод Текст в столбец мастер в экселе. Здесь я покажу вам шаг за шагом, как использовать этот мастер для достижения желаемого результата.

Шаг 1. Выберите ячейки, которые вы хотите разделить, и откройте мастер «Текст в столбцы».

В этом случае я выбираю диапазон A2: A8, который содержит полные имена. Затем перейдите к Данные вкладку нажмите Текст в столбцы для открытия Текст в столбцы Мастер.

Шаг 2. Настройте шаги один за другим в мастере.

1. В Шаг 1 из 3 мастера, выберите разграниченный и нажмите Следующая .
   

   

2. В Шаг 2 из 3 мастер, выберите разделители для ваших данных, а затем нажмите кнопку Следующая Кнопка продолжить.
   
   В этом случае, поскольку мне нужно разделить полные имена на имена и фамилии на основе пробелов, я выбираю только Space флажок в Разделители .

   

   Заметки:

   • Если нужный вам разделитель не показан в этом разделе, вы можете выбрать Другие контрактные услуги установите флажок и введите собственный разделитель в текстовое поле.
   • Чтобы разделить ячейки разрывом строки, вы можете выбрать Другие контрактные услуги флажок и нажмите Ctrl + J ключи вместе.
     
     
3. В последнем мастере нужно настроить следующим образом:
   
   1). Направление выберите ячейку для размещения разделенных данных. Здесь я выбираю ячейку C2.

   2) Нажмите Завершить .

   

Результат

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

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

Как вы можете видеть, Текст в столбцы мастеру требуется несколько шагов для выполнения задачи. Если вам нужен более простой метод, Разделить клетки особенность Kutools for Excel настоятельно рекомендуется. С помощью этой функции вы можете удобно разделить ячейки на несколько столбцов или строк на основе определенного разделителя, выполнив настройки в одном диалоговом окне.

После установки Kutools для Excel, наведите на Кутулс > Слияние и разделение > Разделить клетки для открытия Разделить клетки диалоговое окно.

1. Выделите диапазон ячеек, содержащих текст, который вы хотите разделить.
2. Выберите Разделить на столбцы опцию.
3. Выберите Space (или любой разделитель, который вам нужен) и нажмите OK.
   
   
4. Выберите ячейку назначения и нажмите OK чтобы получить все разделенные данные.
   
   

Разделите ячейки на несколько столбцов с помощью Flash Fill

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

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

В этом случае я собираюсь разделить полные имена в столбце А на отдельные имена и фамилии. Первое полное имя находится в ячейке A2, поэтому я выбираю соседнюю с ней ячейку B2 и ввожу имя. Смотрите скриншот:

Шаг 2. Примените мгновенное заполнение, чтобы автоматически заполнить все имена.

Начните вводить второе имя в ячейку ниже B2 (то есть B3), затем Excel распознает шаблон и создаст предварительный просмотр остальных имен, и вам нужно нажать Enter принять предварительный просмотр.

Теперь все имена полных имен в столбце A разделены в столбце B.

Шаг 3: Получите фамилии полных имен в другом столбце

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

Результат

Разделить ячейки на несколько столбцов с формулами

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

Используйте функции TEXT для разделения ячеек на столбцы с помощью определенного разделителя

Формулы, представленные в этом разделе, доступны во всех версиях Excel. Чтобы применить формулы, сделайте следующее.

Шаг 1. Извлеките текст перед первым разделителем (в данном случае имена)

1. Выберите ячейку (в данном случае C2), чтобы вывести имя, введите следующую формулу и нажмите Enter чтобы получить первое имя в A2.
   

   =LEFT(A2,SEARCH(" ",A2)-1)

   
2. Выберите эту ячейку результата и перетащите ее маркер автозаполнения вниз, чтобы получить остальные имена.
   
   

Шаг 2: Извлеките текст после первого разделителя (в данном случае фамилии)

1. Выберите ячейку (в данном случае D2), чтобы вывести фамилию, введите следующую формулу и нажмите Enter чтобы получить фамилию в A2.
   

   =RIGHT(A2,LEN(A2)-SEARCH(" ",A2))

   
2. Выберите эту ячейку результата и перетащите ее маркер автозаполнения вниз, чтобы получить остальные фамилии.
   
   

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

Если вы используете Excel для Microsoft 365, Функция РАЗДЕЛИТЬ ТЕКСТ больше рекомендуется. Пожалуйста, сделайте следующее.

Шаг 1: Выберите ячейку для вывода результата. Здесь я выбираю ячейку C2

Шаг 2: Введите приведенную ниже формулу и нажмите Enter.

=TEXTSPLIT(A2," ")

Вы можете видеть, что весь текст, разделенный пробелами в A2, разбит на разные столбцы.

Шаг 3. Перетащите формулу, чтобы получить все результаты

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

Разделить ячейки в Excel на несколько строк

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

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

Если вы используете Excel для Microsoft 365, метод функции TEXTSPLIT может легко помочь. Пожалуйста, сделайте следующее.

Шаг 1: Выберите ячейку для вывода результата. Здесь я выбираю ячейку B6

Шаг 2: Введите приведенную ниже формулу и нажмите Enter.

=TEXTSPLIT(A2,,"/")

Весь текст в формате A2 разбит на отдельные строки на основе разделителя «косая черта».

Чтобы разделить данные в ячейках A3 и A4 на отдельные строки на основе косых черт, просто повторите шаги 1 и 2 с соответствующими формулами ниже.

Формула в C6:

=TEXTSPLIT(A3,,"/")

Формула в D6:

=TEXTSPLIT(A4,,"/")

Результат

Удобно разбивать ячейки на несколько строк с помощью Kutools

Хотя функция TEXTSPLIT в Excel очень полезна, она ограничена пользователями Excel для Microsoft 365. Более того, если у вас есть несколько ячеек в столбце для разделения, вам нужно будет применять разные формулы индивидуально к каждой ячейке, чтобы получить результаты. В отличие, Kutools for Excel's Разделить клетки Функция работает во всех версиях Excel. Он обеспечивает простое и эффективное решение для разделения ячеек на несколько строк или столбцов одновременно всего несколькими щелчками мыши.

После установки Kutools для Excel, нажмите Кутулс > Слияние и разделение > Разделить клетки для открытия Разделить клетки диалоговое окно.

1. Выделите диапазон ячеек, содержащих текст, который вы хотите разделить.
2. Выберите Разделить на строки опцию.
3. Выберите нужный вам разделитель (здесь я выбираю Другие контрактные услуги вариант и введите косую черту), затем нажмите OK.
   
   
4. Выберите ячейку назначения и нажмите OK чтобы получить все разделенные данные
   
   

Разделить ячейки на несколько строк с помощью кода VBA

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

Шаг 1. Откройте окно Microsoft Visual Basic для приложений.

Нажмите другой + F11 клавиши, чтобы открыть это окно.

Шаг 2. Вставьте модуль и введите код VBA.

Нажмите Вставить > Модули, а затем скопируйте и вставьте следующий код VBA в Модуль (код) окно.

Код VBA: разделить ячейки на несколько строк в Excel

Option Explicit

Sub SplitCellsToRows()
'Updated by Extendoffice 20230727
    Dim inputRng As Range
    Dim outputRng As Range
    Dim cell As Range
    Dim splitValues() As String
    Dim delimiter As String
    Dim i As Long
    Dim columnOffset As Long
    On Error Resume Next
    
    Set inputRng = Application.InputBox("Please select the input range", "Kutools for Excel", Type:=8) ' Ask user to select input range
    If inputRng Is Nothing Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    Set outputRng = Application.InputBox("Please select the output range", "Kutools for Excel", Type:=8) ' Ask user to select output range
    If outputRng Is Nothing Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    delimiter = Application.InputBox("Please enter the delimiter to split the cell contents", "Kutools for Excel", Type:=2) ' Ask user for delimiter
    If delimiter = "" Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    If delimiter = "" Or delimiter = "False" Then Exit Sub ' If the user clicked Cancel or entered nothing, exit the sub
    
    Application.ScreenUpdating = False
    
    columnOffset = 0
    For Each cell In inputRng
        If InStr(cell.Value, delimiter) > 0 Then
            splitValues = Split(cell.Value, delimiter)
            For i = LBound(splitValues) To UBound(splitValues)
                outputRng.Offset(i, columnOffset).Value = splitValues(i)
            Next i
            columnOffset = columnOffset + 1
        Else
            outputRng.Offset(0, columnOffset).Value = cell.Value
            columnOffset = columnOffset + 1
        End If
    Next cell
    
    Application.ScreenUpdating = True
End Sub

Шаг 3: Запустите код VBA

Нажмите F5 ключ для запуска кода. Затем вам необходимо выполнить следующие настройки.

1. Появится диалоговое окно, предлагающее вам выбрать ячейки с данными, которые вы хотите разделить (здесь я выбираю диапазон A2: A4). Сделав свой выбор, нажмите OK.
   
   
2. Во втором всплывающем диалоговом окне нужно выбрать диапазон вывода (здесь я выбираю ячейку B6), а затем нажать OK.
   
   
3. В последнем диалоговом окне введите разделитель, используемый для разделения содержимого ячейки (здесь я ввожу косую черту), а затем нажмите кнопку OK .
   
   

Результат

Ячейки в выбранном диапазоне разбиваются на несколько строк одновременно.

Разбить ячейки на несколько строк с помощью Power Query

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

Шаг 1. Выберите ячейки, которые вы хотите разделить на несколько строк, выберите «Данные» > «Из таблицы/диапазона».

Шаг 2. Преобразуйте выбранные ячейки в таблицу

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

Шаг 3: выберите «Разделить столбец по разделителю».

A Таблица - Power Query редактор всплывает окно, нажмите Разделить столбец > По разделителю под Главная меню.

Шаг 4. Настройте диалоговое окно «Разделить столбец по разделителю».

1. В Выберите или введите разделитель раздел, укажите разделитель для разделения текста (Здесь я выбираю На заказ и введите косую черту / в текстовом поле).
2. Развернуть Дополнительные параметры раздел (который по умолчанию свернут) и выберите Ряды опцию.
3. В Цитата Персонаж раздел, выбрать Ничто из выпадающего списка;
4. Нажмите OK.
   
   

Шаг 5: Сохраните и загрузите разделенные данные

1. В этом случае, поскольку мне нужно указать пользовательское место назначения для моих разделенных данных, я нажимаю Закрыть и загрузить > Закрыть и загрузить в.
   
   
   Функции: чтобы загрузить разделенные данные на новый рабочий лист, выберите Закрыть и загрузить опцию.
2. В Импортировать данные диалогового окна, выберите Существующий рабочий лист выберите ячейку, чтобы найти разделенные данные, а затем щелкните OK.
   
   

Результат

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

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

Статьи по теме