Often you may want to split comma-separated values into rows in Excel, similar to the following example:
Fortunately this is easy to do with built-in Excel functions and the following step-by-step example shows how to do so.
Step 1: Enter the Data
First, let’s enter the following dataset that contains comma-separated names of basketball players on various teams:
Step 2: Split the Commas-Separated Values
Next, type the following formula into cell D2 to split the comma-separated values in cell B2 into their own columns:
=TEXTSPLIT(B2, ",")
Then click and drag this formula down to each remaining row in column D:
Step 3: Transpose the Values
Next, type the following formula into cell D6 to transpose the values:
=TRANSPOSE(D2:F4)
The following screenshot shows how to use this formula in practice:
Step 4: Split the Values into Rows
The last step is to use the VSTACK function to stack the values from each row into one column.
Type the following formula into cell B6 to do so:
=VSTACK(D6:D8, E6:E8, F6:F8)
The following screenshot shows how to use this formula in practice:
We have now successfully split the comma-separated player names from the original dataset into their own rows.
Note: You can find the complete documentation for the VSTACK function in Excel here.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Count Number of Occurrences in Excel
How to Count Frequency of Text in Excel
How to Calculate Relative Frequency in Excel
