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

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

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

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

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

• С помощью функции TEXTSPLIT (Excel 365)
• Легко с Kutools
• С помощью кода VBA
• С помощью Power Query (Excel 2016 и новее)

Видео

Разделить ячейки в 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, чтобы получить все разделённые данные.
   

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

Теперь перейдём к третьему методу — автозаполнению (Flash Fill). Эта функция появилась в Excel 2013 и предназначена для автоматического заполнения данных при обнаружении определённой закономерности. В этом разделе я покажу, как с помощью автозаполнения (Flash Fill) разделить имя и фамилию, извлечённые из полного имени в одном столбце.

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

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

Шаг 2: примените автозаполнение (Flash Fill), чтобы автоматически заполнить все Имя

Начните вводить второе имя в ячейку под 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 для Microsoft 365, настоятельно рекомендуем воспользоваться функцией TEXTSPLIT. Выполните следующие действия.

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

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

=TEXTSPLIT(A2," ")

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

Шаг 3: протяните формулу, чтобы получить все результаты

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

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

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

Разделить ячейки в несколько строк с помощью функции 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 для 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 правильно определил диапазон выделенных ячеек, отметьте наличие заголовков и нажмите кнопку ОК.
Если выделенные ячейки уже имеют формат таблицы Excel, перейдите к шагу 3.

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

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

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

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

   

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

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

   

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

   

Результат

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

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

Связанные статьи