Table of Contents
This tutorial describes the process of splitting comma-separated values into individual rows in Excel. By following these steps, users will be able to efficiently organize and manage their data by separating each value into its own designated row. This can be particularly useful for data sets with multiple values in a single cell, allowing for easier analysis and manipulation of the data. With clear instructions and examples, this guide aims to help users effectively split comma-separated values into rows in Excel.
Excel: Split Comma-Separated Values into Rows
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: