Зазвичай ви можете використовувати функцію «Текст у стовпець», щоб розділити вміст клітинки на кілька стовпців за певним роздільником, таким як кома, крапка, крапка з комою, скісна риска тощо. Але інколи вам може знадобитися розділити вміст клітинки на кілька рядків. і повторіть дані з інших стовпців, як показано на знімку екрана нижче. Чи є у вас хороші способи вирішення цього завдання в Excel? Цей підручник познайомить вас з деякими ефективними методами виконання цієї роботи в Excel.

Відео: розділення текстових рядків за допомогою розділювача на кілька рядків

Спосіб A. Розділіть розділений текст на кілька рядків за допомогою коду VBA

У цьому розділі я познайомлю два коди VBA, які допоможуть розділити вміст клітинок, розділених певним роздільником.

Розділити текст, розділений комою, пробілом, крапкою з комою тощо.

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

 примітки: Цей код НЕ підтримка розстібати, вам краще створити резервну копію даних перед застосуванням цього коду.

Крок 1. Відкрийте редактор модуля VBA та скопіюйте код

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

2. У вікні, що відкрилося, натисніть Insert > Модулі щоб створити новий порожній модуль.

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. У вікні, що відкрилося, натисніть Insert > Модулі щоб створити новий порожній модуль.

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. Виберіть функцію «Розділити дані на рядки».

Натисніть Кутулс > Злиття та розділення > Розділити дані на рядки, див. скріншот:

Крок 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). Виберіть Кожне входження роздільника з розділу Split at;
• 4). Потім натисніть Додаткові параметри щоб розгорнути розділ, і виберіть ряди під Розділити на;
• 5). Нарешті натисніть OK щоб закрити це діалогове вікно.

3. Тепер вибрані дані було розділено на кілька рядків, як показано на знімку екрана нижче:

Крок 3: Виведіть Power Query до таблиці Excel

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

 Поради: Натисніть Закрити та завантажити виведе дані на новий аркуш; Натисніть Закрити та завантажити до дані будуть виведені на будь-який інший потрібний аркуш.

2. Нарешті, дані будуть завантажені на новий аркуш, дивіться знімок екрана:

 Поради: Якщо вам потрібно часто оновлювати дані у вихідній таблиці, будь ласка, не хвилюйтеся, вам потрібно просто клацнути правою кнопкою миші таблицю результатів і клацнути оновлення динамічно отримувати новий результат.

Статті по темі: