How to Split Comma Separated Values into Rows or Columns in Excel

Sometimes, while working in Excel, we need to split comma-separated continuous values of one column into different columns or rows. When we import data generated from other applications in Excel, all data might be placed in a singular column; separated by a comma. Besides, we may need to extract a certain portion of the data based on the requirement. This article will guide you to split comma-separated values into multiple columns/rows using several functions and features.

How to Split Comma Separated Values into Rows or Columns in Excel: 5 Methods

1. Separate Data into Rows/Columns by Comma Using ‘Text to Columns’ Feature in Excel

1.1. Split Values into Columns

First of all, I will use the Text to Columns feature of Excel to split data into multiple columns. This is the easiest method to split data into multiple columns. Suppose, we have a dataset containing several fruits located in a column, separated by a comma. There are 3 fruits in each cell. Now, I will split the fruits of column B into 3 different columns (columns C, D & E).

Follow the below steps to do the task.

Steps:

• Firstly, select the entire dataset and go to Data > Data Tools > Text to Columns.

• As a result, the Text to Columns Wizard appears. Now, from the Original data type section, choose Delimited and click Next.

• Then from the Delimiters section, put a checkmark on Comma and press Next.

• After that, choose the Destination location (here, Cell C5) and press Finish.

• Finally, after closing the Text to Columns Wizard, we will get the below result. All the data are separated by commas are split into columns C, D, and E.

1.2. Split into Rows Using Text to Columns

Now, I will split data in a single cell into multiple rows separated by a comma. Suppose, I have the below dataset containing some fruit names. Before putting these values into multiple rows, I will split them into columns using the Text to Columns feature.

Steps:

• First, select Cell B5, go to Data > Text to Columns.
• Then from the Text to Columns Wizard select Original Data Type: Delimited and click Next.
• Now choose the Delimiters type: Comma and click Next.
• After that, choose the Destination cell (here Cell C5) and press Finish.
• As a result, you will get the below output. Now, I will place the resulting data in multiple rows. To do that copy the range C5:G5.

• Later right-click on Cell B7, and choose the Transpose from Paste Options (see screenshot).

• Finally, we will get the result we were looking for, all the comma-separated values are split over rows 7 to 11.

2. Excel Power Query to Split Comma Separated Values into Columns or Rows

2.1. Power Query to Split Values into Columns

This time, I will use excel Power Query to split comma-separated data into multiple columns. Follow the below instructions to perform the task.

Steps:

• First, put the cursor in any of the cells in the existing dataset. Then go to Data > From Table/Range (Get & Transform Data group).

• Consequently, Excel will ask you to convert the data range into a table. Check the data range and press OK to create the table.

• As a result, the Power Query Editor window appears with the below table. Now, from the Power Query Editor window go to Home > Split Column > By Delimiter.

• After that, the Split Column by Delimiter dialog shows up. Choose Comma from the Select or enter delimiter and press OK (see screenshot).

• As a consequence, excel splits the table into 3 columns as below. Now, to close the Power Query Editor, go to Home > Close & Load > Close & Load.

• Finally, here is the ultimate result we receive. All the comma-separated data are split into 3 columns of a table.

2.2. Split Data into Rows

Here, I will split values separated by comma into multiple rows using Excel Power Query.

Steps:

• We have some comma-separated fruits in Cell B5 & C5. To apply Power Query to these values, click on Cell B5 or C5, and go to Data > From Table/Range.

• Next, the Create Table dialog will show up, check the table range, and press OK. Consequently, the below table will be created in the Power Query Editor window.
• Then select the table, go to Home > Split Column > By Delimiter.

• Now Split Column by Delimiter dialog appears. From the Select or enter delimiter section choose Comma,  go to Advanced Options, and click Rows from the field: Split into. When you are done press OK.

• Upon pressing OK, we will get the below output. Go to Home > Close & Load > Close & Load to display the result on the excel worksheet.

• Eventually, the following is the ultimate result we got. All the comma-separated values are split into rows 2 to 7.

3. Combine LEFT, RIGHT, MID, FIND & LEN Functions for Splitting Comma Separated Values into Columns

We can use Excel functions to split data separated by commas into multiple columns. Basically, we can extract portions of data in different columns using combinations of Excel functions based on a delimiter (comma, space, semicolon). In this method, I will extract data from 3 positions from a continuous text string located in column B and place them in multiple columns.

3.1. Find First Word

Initially, I will extract the first word from the continuous text string using the LEFT and FIND functions.

Steps:

• Type the below formula in Cell C5 and press Enter from the keyboard.

• Upon entering the formula, Excel will return ‘Dates’ which is the first word of Cell B5.

Here, the FIND function returns the location of the 1st comma. Then the LEFT function extracts the first word before the first comma.

3.2. Extract 2nd Word

Now, I will use the combination of MID and FIND functions to extract the second word from Cell B5.

Steps:

• Type the following formula in Cell D5 and hit Enter.

• As a consequence, the above formula returns Grapes; the 2nd word of Cell B5.

Here, the MID function returns the characters from the middle of the text string of Cell B5. and the FIND function returns the location of the 2nd string in Cell B5.

3.3. Find 3rd Word

Suppose, I will extract the 3rd word from Cell B5 based on the location of the comma. While extracting the 3rd word, I will use the RIGHT, LEN, and FIND functions.

Steps:

• Type the below formula in Cell D5. Then press Enter.

• Once you hit Enter, Excel will return Watermelon which is the 3rd word of our continuous data in Cell B5.

Here, the LEN function returns the length of Cell B5. Then the FIND function returns the location of the comma in Cell B5. Later, depending on the result of the FIND and LEN functions the RIGHT function extracts the rightmost word from Cell B5.

• As I have received all 1st, 2nd, and 3rd words in different columns for the first row, I will try to get a similar result for the rest of the rows. To do that, select the range C5:D5 and use the Fill Handle (+) tool.

• Eventually, here is the final result we will receive.

4. Excel VBA to Split up Comma Separated Values into Columns or Rows

4.1. VBA to Split Values into Columns

You can split data separated by a comma into multiple columns using a simple VBA code.

Steps:

• First, go to the worksheet where you want to split the data. Next, right-click on the sheet name and click View Code to bring up the VBA window.

• Then type the below code in the Module and Run the code by using the F5 key.

Sub SplitData()

Dim Range() As String, Count As Long, x As Variant
For r = 5 To 10
Range = Split(Cells(r, 2), ",")
Count = 3
For Each x In Range
Cells(r, Count) = x
Count = Count + 1
Next x
Next r

End Sub

Here ‘r‘ indicates rows that contain data. On the other hand, ‘Count=3‘ indicates column C, which is the first column to display the split data.

• Once you run the code, data separated by commas in column B are split into columns C, D, and E as below:

Read More: Excel Macro to Split Data into Multiple Files

4.2. Split Values into Rows

Now I will split comma-separated values into different rows using Excel VBA. Follow the below steps to perform the task.

Steps:

• First, go to the worksheet where you have the data, and right-click on the View Code.

• As a consequence, the VBA window appears. Write the below code in the Module and run the code by pressing F5 on the keyboard.

Sub SplitRows()

Dim rng As Range
Dim rng1 As Range
Dim cell As Range
Dim N As Long
Dim address As String
Dim update As Boolean
Dim ret As Variant
On Error Resume Next
address = Application.ActiveWindow.RangeSelection.address
Set rng = Application.InputBox("Please enter a range", "Input Box", address, , , , , 8)
Set rng = Application.Intersect(rng, rng.Worksheet.UsedRange)
If rng Is Nothing Then Exit Sub
If rng.Columns.Count > 1 Then
MsgBox "Cannot select more than one column"
Exit Sub
End If
Set rng1 = Application.InputBox("Destination Cell", "Input Box", , , , , , 8)
Set rng1 = rng1.Range("A1")
If rng1 Is Nothing Then Exit Sub
update = Application.ScreenUpdating
Application.ScreenUpdating = False
For Each cell In rng
ret = Split(cell.Value, ",")
rng1.Worksheet.Range(rng1.Offset(N, 0), rng1.Offset(N + UBound(ret, 1), 0)) = Application.WorksheetFunction.Transpose(ret)
N = N + UBound(ret, 1) + 1
Next
Application.ScreenUpdating = update

End Sub

• Now when you run the code the below input box will appear, enter the below data range, and press OK.

• As a result, another input box will appear. Insert the destination cell there and press OK.

• In the end, we will get the below output. All the comma-separated values of our dataset are split into rows 8 to 13.

5. Use Excel Flash Fill to Split Comma Separated Values into Different Columns

We can type a portion of the comma-separated data in a different column and then apply the Flash Fill feature to get the rest of the data of the same pattern.

Steps:

• Type ‘Dates’ in Cell C5. Later, when you start to type ‘R’  in Cell C6, excel understands that you want fruits in the first position from all the rows.

• Simply press Enter to get the below result. Now you can apply this similar method to split other comma-separated values into multiple columns.

Read More: Split Data into Multiple Worksheets in Excel

Download Practice Workbook

Conclusion

In the above article, I have tried to discuss several methods to split comma-separated values into rows or columns in Excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

Related Articles

• How to Split Data into Equal Groups in Excel