Table of Contents
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
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 .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: