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

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

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

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

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

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

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

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

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

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. Затем щелкните OK, появится еще одно окно подсказки, чтобы напомнить вам о вводе разделителя, на основе которого вы хотите разделить данные. Здесь я набираю запятую и пробел (, ), см. снимок экрана:

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

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

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

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

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

1. Нажмите Alt + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

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. Затем щелкните OK Кнопка, данные в выбранных ячейках разбиваются на строки, как показано ниже:

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

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

Шаг 1. Выберите функцию «Разбить данные на строки».

Нажмите Кутулс > Слияние и разделение > Разделить данные на строки, см. снимок экрана:

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

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

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

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

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

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

Метод C. Разбейте текст с разделителями на несколько строк, Power Query

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

Шаг 1. Загрузите таблицу данных в Power Query

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

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

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

3. Сейчас Power Query редактор отображается окно с данными, см. скриншот:

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

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

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

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

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

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

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

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

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

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

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

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

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

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