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

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

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

• С помощью мастера Текст по столбцам
• Просто с помощью Kutools
• С помощью функции Быстрое заполнение (Excel2013 и новее)
• С помощью формул

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

• С помощью функции TEXTSPLIT (Excel365)
• Просто с помощью Kutools
• С помощью VBA-кода
• С помощью Power Query (Excel2016 и новее)

Видео

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

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

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

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

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

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

Шаг2. Настройте параметры мастера поочерёдно

1. В Шаг1 из3 мастере выберите С разделителями и затем нажмите Далее кнопку.
   

   

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

   

   Примечания:

   • Если нужного разделителя нет в этом разделе, вы можете выбрать флажок Другой и ввести свой разделитель в текстовое поле.
   • Чтобы разделить ячейки по разрыву строки, выберите Другой флажок и нажмите Ctrl + J одновременно.
     

     

3. В последнем окне мастера выполните следующие настройки:
   
   1) В поле Назначение выберите ячейку для размещения разделённых данных. Здесь я выбираю ячейку C2.

   2) Нажмите кнопку Завершить.

   

Результат

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

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

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

После установки Kutools для Excel выберите Kutools > Объединить и разделить > Разделить ячейки, чтобы открыть диалоговое окно Разделить ячейки.

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

   

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

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

Теперь перейдём к третьему способу — функции Быстрое заполнение. Она появилась в Excel2013 и предназначена для автоматического заполнения данных при обнаружении закономерности. В этом разделе я покажу, как использовать Быстрое заполнение для разделения имени и фамилии из полного имени в одном столбце.

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

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

Шаг2. Примените Быстрое заполнение для автоматического заполнения всех имён

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

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

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

Повторите шаги1 и2, чтобы разделить фамилии из полных имён в столбце A в столбец, следующий за столбцом с именами.

Результат

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

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

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

Формулы, приведённые в этом разделе, работают во всех версиях 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 для Microsoft365, рекомендуется воспользоваться функцией TEXTSPLIT. Выполните следующие шаги.

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

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

=TEXTSPLIT(A2," ")

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

Шаг3. Протяните формулу для получения всех результатов

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

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

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

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

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

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

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

=TEXTSPLIT(A2,,"/")

Весь текст из A2 разделён на отдельные строки по разделителю «косая черта».

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

Формула в C6:

=TEXTSPLIT(A3,,"/")

Формула в D6:

=TEXTSPLIT(A4,,"/")

Результат

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

Хотя функция TEXTSPLIT в Excel очень полезна, она доступна только пользователям Excel для Microsoft365. Кроме того, если вам нужно разделить несколько ячеек в столбце, для каждой ячейки придётся использовать отдельную формулу. В отличие от этого, функция Разделить ячейки в Kutools для Excel работает во всех версиях Excel. Она предлагает простой и эффективный способ разделить ячейки на несколько строк или столбцов одновременно всего за несколько кликов.

После установки Kutools для Excel нажмите Kutools > Объединить и разделить > Разделить ячейки, чтобы открыть диалоговое окно Разделить ячейки.

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

   

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

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

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

Шаг1. Откройте окно Microsoft Visual Basic for Applications

Нажмите клавиши Alt + 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. Выберите Разделить столбец по разделителю

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

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

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

   

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

1. В данном случае, так как мне нужно указать пользовательское место для разделённых данных, я нажимаю Закрыть и загрузить > Закрыть и загрузить в.
   

   

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

   

Результат

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

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

Похожие статьи