Excel: How to split delimited text to rows
Let’s say here is a table with three columns in a worksheet, the first one lists the price levels, the second one lists fruit names with commas delimited in cells, and the third column lists the prices of the fruits. The job is to split the delimited fruit names into rows, and repeat the related price levels and prices rows as below screenshot shown:
Here introduce two methods to solve this job.
Here is a VBA code that can quickly split delimited text to rows.
1. Press Alt + F11 keys to enable the Microsoft Visual Basic for Applications window.
2. Click Insert tab > Module to create a new blank module.
3. Copy and paste below code to the blank module.
VBA: Split delimited text to rows
Public Sub SplitTextInCellsToRows() 'UpdatebyExtendoffice20220622 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) Debug.Print xRg.Address xArr = Split(xRg, xSplitChar) 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(xFFNum) Next xRg.EntireRow.Delete Next Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
4. Press F5 key to click the run button to activate the code, a dialog pops out for you to select the column that excludes the header that contains the delimited text needed to be split into rows. Click OK.
5. Then the second dialog pops to enter the delimiter you use. Here types comma (,). Click OK.
Now the delimited texts have been split into rows and other related columns have been repeated.
The VBA will change the original data, you should better save a copy of it before.
If you are in Excel 2013 or later versions, the Power Query will be a good choice for solving this job.
1. Select the data range, to click Data > From Table/Range.
2. In the popping Create Table dialog, check My table has headers as you need, and click OK.
Then the Power Query Editor window is displayed, and the data has been shown in it.
3. Click on the column that you want to split the delimited texts to rows, and click Transform tab > Split Column > By Delimiter.
Or you also can select the column and right-click on the column header to choose this feature in the context menu.
4. In the Split Column by Delimiter window, choose the delimiter as you need from the Select or enter delimiter section, then choose Each occurrence of the delimiter option, and then expand Advanced options section to choose Rows option. Click OK.
Now the delimited texts have been split into rows.
5. Then close the window, a dialog pops out, click Keep button.
Then a new sheet will be created with the table.