Normally, you can use the Text to Column feature to split cell contents into multiple columns by a specific delimiter, such as comma, dot, semicolon, slash, etc. But, sometimes, you may need to split the delimited cell contents into multiple rows and repeat the data from other columns as below screenshot shown. Do you have any good ways for dealing with this task in Excel? This tutorial will introduce some effective methods to complete this job in Excel.

Video: Split text strings by delimiter into multiple rows

Method A: Split delimited text into multiple rows by VBA code

In this section, I will introduce two VBA codes to help split the cell contents which are separated by a certain delimiter.

Split text separated by comma, space, semicolon, etc.

To split the text strings which are separated by some normal delimiter, such as comma, space, semicolon, slash, etc., the following code can do you a favor. Please follow the below steps:

 Note: This code doesn't support Undo, you’d better backup the data before applying this code.

Step 1: Open the VBA module editor and copy the code

1. Activate the sheet which you want to use. And then, press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the opened window, click Insert > Module to create a new blank module.

3. Then, copy and paste the below code into the blank module.

VBA code: Split text by specific delimiter (comma, dot, space, etc.)

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   

Step 2: Execute the code to get the result

1. After pasting the code, please press F5 key to run this code. Then, a prompt box will pop out to remind you selecting the cells contain the delimited text that you want to split, see screenshot:

2. Then, click OK, another prompt box is popped out to remind you typing the separator you want to split the data based on. Here, I type a comma and a space (, ), see screenshot:

3. At last, click OK button. Now, you will see the selected text strings are split into rows based on the comma and other relative columns data are repeated as below screenshots shown:

Split text separated by line break

If your cell contents are separated by line breaks, to split them into multiple rows, here is another VBA code which can help you.

 Note: This code doesn't support Undo you’d better backup the data before applying this code.

Step 1: Open the VBA module editor and copy the code

1. Press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the opened window, click Insert > Module to create a new blank module.

3. Then, copy and paste the below code into the blank module.

VBA code: Split text by line break

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 

Step 2: Execute the code to get the result

1. After pasting the code, please press F5 key to run this code. In the popped-out box, please select the cells that you want to split, see screenshot:

2. Then, click OK button, the data in the selected cells are split into rows as below screenshot shown:

Method B: 10s to split delimited text into multiple rows by Kutools for Excel

If you have installed Kutools for Excel, with its Split Data to Rows feature, you can split the text strings into multiple rows by any delimiter you specified. Please do with the following steps:

Step 1: Select the Split Data to Rows feature

Click Kutools > Merge & Split > Split Data to Rows, see screenshot:

Step 2: Specify the data cells and delimiter for splitting

In the popped-out dialog box, use the following options:

• 1). Select the list of cells that you want to split from the Range (single column) text box;
• 2). Then, choose the delimiter which separates your data, here, I will choose Other and type comma and a space (, ) into the textbox;
• 3). At last, click OK button.

Now, the selected cells with delimited text strings have been converted into multiple rows based on the specific separator, see screenshot:

 Tips: If you want to restore the original data, you just need to press Ctrl + Z for undoing.

Easy to use? Interested in this feature, please click to download to get a free trial for 30 days.

Method C: Split delimited text into multiple rows by Power Query

If you’re running Office 365 or Excel 2016 and later versions, Power Query is a powerful tool which can help you to split delimited text into multiple rows or columns. It is useful if you want the split data to be refreshed when your original data changes. Please do with the following steps to finish it:

Step 1: Get the data table into Power Query

1. Select the data range that you want to use, then click Data > From Table, see screenshot:

 Tips: In Excel 2019 and Office 365, click Data > From Table/Range.

2. In the popped-out Create Table dialog box, click OK button to create a table, see screenshot:

3. Now, the Power Query Editor window is displayed with the data, see screenshot:

Step 2: Do the transformations in Power Query

1. Select the column that you want to split. And then, click Home > Split Column > By Delimiter, see screenshot:

2. In the Split Column by Delimiter dialog box:

To split the text strings by comma, space, semicolon, etc. please do as this:

• 1). Choose the delimiter your data separated by from the Select or enter delimiter drop down list;
• 2). Select Each occurrence of the delimiter from the Split at section;
• 3). Then, click Advanced options to expand the section, and select Rows under the Split into;
• 4). At last, click OK to close this dialog box.

To split text strings into multiple rows by link break, please do as this:

• 1). Choose Custom from the Select or enter delimiter drop down list;
• 2). From the Insert special characters drop down, click Line Feed, and the characters #(lf) will be inserted into the text box under the Custom automatically;
• 3). Select Each occurrence of the delimiter from the Split at section;
• 4). Then, click Advanced options to expand the section, and select Rows under the Split into;
• 5). At last, click OK to close this dialog box.

3. Now, the selected data has been split into multiple rows as below screenshot shown:

Step 3: Output the Power Query to an Excel table

1. And then, you should output the data into your worksheet. Please click Home > Close & Load > Close & Load / Close & Load To, (here, I will click Close & Load), see screenshot:

 Tips: Click Close & Load will output the data to a new worksheet; Click Close & Load To option, the data will be output to any other sheet you need.

2. Finally, the data will be loaded to a new worksheet, see screenshot:

 Tips: If you need to update your data in the original table frequently, please don’t worry, you just need to right click the result table and click Refresh to get the new result dynamically.

Related Articles: