Split comma separated values into rows/columns in Excel - easy guide

In certain situations, you might find yourself needing to split comma-separated values from selected cells into individual rows or columns. This guide will explore different effective methods to help you achieve this, providing step-by-step instructions to ensure you can efficiently manage and reorganize your data according to your needs.

Split comma separated values into different columns

• With Text to Column wizard
• Easily with Kutools (split by any delimiter)

Split comma separated values into different rows

• With VBA code
• Easily with Kutools (split by any delimiter)

Split comma separated values into different columns

To split comma separated values in selected cells into different columns, try the methods in this section.

Use the Text to Column wizard to split comma separated values into columns

To split comma separated values in selected cells into columns, the commonly used method is the Text to Column wizard in Excel. Here, I will show you step-by-step how to use this wizard to achieve the desired result.

1. Select the range of cells you want to split values into columns, and then click "Data" > "Text to Columns". See screenshot:

2. In the first "Convert Text to Columns Wizard" dialog box, select the "Delimited" option, and then click the "Next" button.

3. In the second "Convert Text to Columns Wizard" dialog box, only check the "Comma" box in the "Delimiters" section, and click the "Next" button.

4. In the last "Convert Text to Columns Wizard" dialog box, select a cell for locating the splitting values in the "Destination" box, and finally click the "Finish" button.

Now all the values in selected cells which were separated by commas are split to different columns as bellow screenshot shown.

Easily split comma separated values into multiple columns with Kutools

As you can see, the "Text to Columns" wizard requires multiple steps to complete the task. If you need a simpler method, the "Split Cells" feature of "Kutools for Excel" is highly recommended. With this feature, you can conveniently split cells into multiple columns or rows based on a specific delimiter, by completing the settings in a single dialog box.

After installing Kutools for Excel, select "Kutools" > "Merge & Split" > "Split Cells" to open the "Split Cells" dialog box.

1. Select the range of cells containing the text you wish to split.
2. Select the "Split to Columns" option.
3. Select "Comma" (or any delimiter you need) and click "OK".
   
   
4. Select a destination cell and click "OK" to get all split data.
   
   

Split comma separated values into different rows

This section demonstrates two methods to help you split comma separated values into different rows in Excel. Please do as follows.

Split comma separated values into multiple rows with VBA

For splitting comma separated values into rows, you can apply the following VBA code.

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

2. In the "Microsoft Visual Basic for Applications" window, click "Insert" > "Module". Then copy and paste the below VBA code into the Module window.

VBA code: Split comma separated values into rows

Sub SplitAll()
	Dim xRg As Range
	Dim xRg1 As Range
	Dim xCell As Range
	Dim I As Long
	Dim xAddress As String
	Dim xUpdate As Boolean
	Dim xRet As Variant
	On Error Resume Next
	xAddress = Application.ActiveWindow.RangeSelection.Address
	Set xRg  = Application.InputBox("Please select a range", "Kutools for Excel", xAddress, , , , , 8)
	Set xRg  = Application.Intersect(xRg, xRg.Worksheet.UsedRange)
	If xRg Is Nothing Then Exit Sub
		If xRg.Columns.Count > 1 Then
			MsgBox "You can't select multiple columns", , "Kutools for Excel"
			Exit Sub
			End If
			Set xRg1 = Application.InputBox("Split to (single cell):", "Kutools for Excel", , , , , , 8)
			Set xRg1 = xRg1.Range("A1")
			If xRg1 Is Nothing Then Exit Sub
				xUpdate = Application.ScreenUpdating
				Application.ScreenUpdating = False
				For Each xCell In xRg
					xRet = Split(xCell.Value, ",")
					xRg1.Worksheet.Range(xRg1.Offset(I, 0), xRg1.Offset(I + UBound(xRet, 1), 0)) = Application.WorksheetFunction.Transpose(xRet)
					I = I + UBound(xRet, 1) + 1
				Next
				Application.ScreenUpdating = xUpdate
			End Sub

3. Press the "F5" key to run the code. In the popping up "Kutools for Excel" dialog box, select the cells you want to split, and then click the "OK" button.

4. In the second popping up "Kutools for Excel" dialog box, select a cell for locating the splitting values, then click "OK".

Then you can see the comma separated values in selected cells are split into rows as bellow screenshot shown.

Easily split comma separated values into rows with Kutools for Excel

VBA code is too hard for Excel newbi to modify for meeting their needs. Here the "Split Cells" feature of "Kutools for Excel" can also help to easily split comma separated values into multiple rows in Excel.

After installing Kutools for Excel, select "Kutools" > "Merge & Split" > "Split Cells" to open the "Split Cells" dialog box.

1. Select the range of cells containing the comma separated values you wish to split.
2. Select the "Split to Rows" option.
3. Select "Comma" (or any delimiter you need) and click "OK".
   
   
4. Select a destination cell and click "OK" to get all split data.
   
   

Demo: Quickly split comma separated values into rows or columns with Kutools for Excel