Разделение текстовых строк по разделителю на несколько строк - 3 быстрых способа

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

Видео: Разделение текстовых строк по разделителю на несколько строк

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

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

Разделение текста, разделенного запятыми, пробелами, точками с запятой и т.д.

Чтобы разделить текстовые строки, разделенные некоторыми обычными разделителями, такими как запятая, пробел, точка с запятой, косая черта и т.д., следующий код может помочь вам. Пожалуйста, следуйте указанным ниже шагам:

 Примечание: Этот код не поддерживает отмену действий, поэтому лучше создать резервную копию данных перед применением этого кода.

Шаг 1: Откройте редактор модуля VBA и скопируйте код

1. Активируйте лист, который вы хотите использовать. Затем нажмите клавиши "Alt" + "F11", чтобы открыть окно "Microsoft Visual Basic for Applications".

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

3. Затем скопируйте и вставьте приведенный ниже код в пустой модуль.

Код VBA: Разделение текста по определенному разделителю (запятая, точка, пробел и т.д.)

Sub SplitTextIntoRows()
'UpdatebyExtendoffice
    Dim xSRg, xIptRg, xCrRg, xRg As Range
    Dim xSplitChar As String
    Dim xArr As Variant
    Dim xFNum, xFFNum, xRow, xColumn, xNum As Integer
    Dim xWSh As Worksheet
    Set xSRg = Application.InputBox("Select a range:", "Kutools for Excel", , , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xSplitChar = Application.InputBox("Type delimiter:", "Kutools for Excel", , , , , , 2)
    If xSplitChar = "" Then Exit Sub
    Application.ScreenUpdating = False
    xRow = xSRg.Row
    xColumn = xSRg.Column
    Set xWSh = xSRg.Worksheet
    For xFNum = xSRg.Rows.Count To 1 Step -1
        Set xRg = xWSh.Cells.Item(xRow + xFNum - 1, xColumn)
        xArr = Split(xRg, xSplitChar)
        xIndex = UBound(xArr)
        For xFFNum = LBound(xArr) To UBound(xArr)
            xRg.EntireRow.Copy
            xRg.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown
            xRg.Worksheet.Cells(xRow + xFNum, xColumn) = xArr(xIndex)
            xIndex = xIndex - 1
        Next
        xRg.EntireRow.Delete
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub   

Шаг 2: Выполните код для получения результата

1. После вставки кода нажмите клавишу "F5", чтобы запустить этот код. Затем появится диалоговое окно с запросом выбрать ячейки, содержащие текст с разделителями, который вы хотите разделить, см. скриншот:

2. Затем нажмите "ОК", появится другое диалоговое окно с запросом ввести разделитель, по которому вы хотите разделить данные. Здесь я ввожу запятую и пробел (, ), см. скриншот:

3. Наконец, нажмите кнопку "ОК". Теперь вы увидите, что выбранные текстовые строки были разделены на строки на основе запятой, а данные из других соответствующих столбцов повторены, как показано на скриншотах ниже:

Разделение текста, разделенного разрывом строки

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

 Примечание: Этот код не поддерживает отмену действий, поэтому лучше создать резервную копию данных перед применением этого кода.

Шаг 1: Откройте редактор модуля VBA и скопируйте код

1. Нажмите клавиши "Alt" + "F11", чтобы открыть окно "Microsoft Visual Basic for Applications".

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

3. Затем скопируйте и вставьте приведенный ниже код в пустой модуль.

Код VBA: Разделение текста по разрыву строки

Sub SplitTextIntoRows()
'UpdatebyExtendoffice
    Dim xSRg, xIptRg, xCrRg, xRg As Range
    Dim xSplitChar As String
    Dim xArr As Variant
    Dim xFNum, xFFNum, xRow, xColumn, xNum As Integer
    Dim xWSh As Worksheet
    Set xSRg = Application.InputBox("Select a range:", "Kutools for Excel", , , , , , 8)
    If xSRg Is Nothing Then Exit Sub
    xSplitChar = Chr(10)
    Application.ScreenUpdating = False
    xRow = xSRg.Row
    xColumn = xSRg.Column
    Set xWSh = xSRg.Worksheet
    For xFNum = xSRg.Rows.Count To 1 Step -1
        Set xRg = xWSh.Cells.Item(xRow + xFNum - 1, xColumn)
        xArr = Split(xRg, xSplitChar)
        xIndex = UBound(xArr)
        For xFFNum = LBound(xArr) To UBound(xArr)
            xRg.EntireRow.Copy
            xRg.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown
            xRg.Worksheet.Cells(xRow + xFNum, xColumn) = xArr(xIndex)
            xIndex = xIndex - 1
        Next
        xRg.EntireRow.Delete
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub 

Шаг 2: Выполните код для получения результата

1. После вставки кода нажмите клавишу "F5", чтобы запустить этот код. В появившемся окне выберите ячейки, которые вы хотите разделить, см. скриншот:

2. Затем нажмите кнопку "ОК", данные в выбранных ячейках будут разделены на строки, как показано на скриншоте ниже:

Метод B: 10 секунд для разделения текста с разделителями на несколько строк с помощью Kutools для Excel

Если вы установили "Kutools для Excel", с помощью его функции "Разделить данные на строки" вы можете разделить текстовые строки на несколько строк с помощью любого указанного вами разделителя. Пожалуйста, выполните следующие шаги:

Шаг 1: Выберите функцию "Разделить данные на строки"

Нажмите "Kutools" > "Объединить и разделить" > "Разделить данные на строки", см. скриншот:

Шаг 2: Укажите ячейки данных и разделитель для разделения

В появившемся диалоговом окне используйте следующие параметры:

• 1). Выберите список ячеек, которые вы хотите разделить, из текстового поля "Диапазон (один столбец)";
• 2). Затем выберите разделитель, который разделяет ваши данные, здесь я выберу "Другой" и введу запятую и пробел (, ) в текстовое поле;
• 3). Наконец, нажмите кнопку "ОК".

Теперь выбранные ячейки с текстовыми строками, разделенными разделителями, были преобразованы в несколько строк на основе конкретного разделителя, см. скриншот:

 Советы: Если вы хотите восстановить исходные данные, просто нажмите Ctrl + Z для отмены.

Легко использовать? Заинтересованы в этой функции? Пожалуйста, нажмите, чтобы загрузить бесплатную пробную версию на 30 дней.

Метод C: Разделение текста с разделителями на несколько строк с помощью Power Query

Если вы используете Office 365 или Excel 2016 и более поздние версии, "Power Query" является мощным инструментом, который поможет вам разделить текст с разделителями на несколько строк или столбцов. Это полезно, если вы хотите, чтобы разделенные данные обновлялись при изменении исходных данных. Пожалуйста, выполните следующие шаги для завершения:

Шаг 1: Получите таблицу данных в Power Query

1. Выберите диапазон данных, который вы хотите использовать, затем нажмите "Данные" > "Из таблицы", см. скриншот:

 Советы: В Excel 2019 и Office 365 нажмите Данные > Из таблицы/диапазона.

2. В появившемся диалоговом окне "Создать таблицу" нажмите кнопку "ОК", чтобы создать таблицу, см. скриншот:

3. Теперь окно "Редактор Power Query" отображается с данными, см. скриншот:

Шаг 2: Выполните преобразования в Power Query

1. Выберите столбец, который вы хотите разделить. Затем нажмите "Главная" > "Разделить столбец" > "По разделителю", см. скриншот:

2. В диалоговом окне "Разделить столбец по разделителю":

Чтобы разделить текстовые строки по запятой, пробелу, точке с запятой и т.д., сделайте следующее:

• 1). Выберите разделитель, по которому разделены ваши данные, из выпадающего списка "Выберите или введите разделитель";
• 2). Выберите "Каждое вхождение разделителя" в разделе "Разделить по";
• 3). Затем нажмите "Расширенные параметры", чтобы развернуть раздел, и выберите "Строки" в разделе "Разделить на";
• 4). Наконец, нажмите "ОК", чтобы закрыть это диалоговое окно.

Чтобы разделить текстовые строки на несколько строк по разрыву строки, сделайте следующее:

• 1). Выберите "Пользовательский" из выпадающего списка "Выберите или введите разделитель";
• 2). Из выпадающего списка "Вставить специальные символы" нажмите "Разрыв строки", и символы "#(lf)" будут автоматически вставлены в текстовое поле под "Пользовательским";
• 3). Выберите "Каждое вхождение разделителя" в разделе "Разделить по";
• 4). Затем нажмите "Расширенные параметры", чтобы развернуть раздел, и выберите "Строки" в разделе "Разделить на";
• 5). Наконец, нажмите "ОК", чтобы закрыть это диалоговое окно.

3. Теперь выбранные данные были разделены на несколько строк, как показано на скриншоте ниже:

Шаг 3: Выведите результаты Power Query в таблицу Excel

1. Затем вы должны вывести данные на ваш рабочий лист. Пожалуйста, нажмите "Главная" > "Закрыть и загрузить" > "Закрыть и загрузить / Закрыть и загрузить в", (здесь я нажму "Закрыть и загрузить"), см. скриншот:

 Советы: Нажатие "Закрыть и загрузить" выведет данные в новый рабочий лист; Нажатие "Закрыть и загрузить в" позволит вывести данные в любой другой лист, который вам нужен.

2. Наконец, данные будут загружены в новый рабочий лист, см. скриншот:

 Советы: Если вам нужно часто обновлять данные в исходной таблице, не беспокойтесь, просто щелкните правой кнопкой мыши по таблице результатов и нажмите "Обновить", чтобы динамически получить новые результаты.

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