You can use the following formula in Excel to split a cell vertically into multiple cells:
=TEXTSPLIT(A2,, ", ")
This particular formula splits the values in cell A2 vertically into multiple cells, using a comma as the delimiter to determine where to split the values.
The following example shows how to use this formula in practice.
Example: How to Split a Cell Vertically in Excel
Suppose we have the following list of basketball team names in cell A2 in Excel:
Suppose we would like to split the team names in this cell vertically into multiple cells.
We can type the following formula into cell C2 to do so:
=TEXTSPLIT(A2,, ", ")
The following screenshot shows how to use this formula in practice:
Column C now contains each of the individual team names from cell A2 split into multiple cells vertically.
How This Formula Works
The TEXTSPLIT function in Excel uses the following syntax:
TEXTSPLIT(text, col_delimiter, row_delimiter, …)
where:
- text_col: The text you want to split
- col_delimiter: The delimiter to split text across columns
- row_delimiter: The delimiter to split text across rows
By using the formula =TEXTSPLIT(A2,, “, “) we tell Excel to split the text in cell A2 across rows wherever a comma occurs.
Since we didn’t specify a delimiter to use for the columns, Excel only splits the text vertically down the rows instead of horizontally across columns.
Note that you could also specify a different delimiter to use besides a comma if the text values in your cell are separated by a different delimiter.
Note: You can find the complete documentation for the TEXTSPLIT function in Excel here.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Split Date into Day, Month and Year
Excel: How to Split String Based on Multiple Delimiters
Excel: How to Split Word into Individual Letters
