Split Text Strings by Delimiter into Multiple Rows - 3 Quick Tricks
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.
In this section, I will introduce two VBA codes to help split the cell contents which are separated by a certain delimiter.
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:
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:
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:
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.
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:
To split text strings into multiple rows by link break, please do as this:
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.