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

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

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

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

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

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

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

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

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

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

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

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 for Applications.

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

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: Разделение текста с разделителями на несколько строк за 10 секунд с помощью Kutools для Excel

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

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

Нажмите «Kutools» > «Merge & Split» > «Разделить на несколько строк» (см. скриншот):

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

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

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

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

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

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

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

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

Шаг 1: Импортируйте таблицу данных в Power Query

1. Выделите диапазон данных, который вы хотите использовать, и нажмите «Data» > «From Table» (см. скриншот):

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

2. В появившемся диалоговом окне «Create Table» нажмите кнопку «OK», чтобы создать таблицу (см. скриншот):

3. Теперь откроется окно «Power Query Editor» с вашими данными (см. скриншот):

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

1. Выделите столбец, который нужно разделить, и нажмите «Home» > «Split Column» > «By Delimiter» (см. скриншот):

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

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

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

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

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

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

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

1. Затем выведите данные на лист: нажмите «Home» > «Close & Load» > «Close & Load / Close & Load To» (в данном случае выбрано «Close & Load») (см. скриншот).

Совет: при выборе «Close & Load» данные загружаются на новый лист, а при выборе «Close & Load To» — на любой другой лист по вашему усмотрению.

2. В итоге данные будут загружены на новый лист (см. скриншот):

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

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