You can use the following formula in Google Sheets to split the text in a cell into rows:
=TRANSPOSE(SPLIT(A2, " "))
This particular example will split the text in cell A2 into rows, using a space as a delimiter.
If the text in your cell is separated by a different delimiter, such as a comma, you can use the following formula instead:
=TRANSPOSE(SPLIT(A2, ","))
And if you have multiple cells with text that you’d like to split into rows, you can wrap an ARRAYFORMULA around the SPLIT function:
=ARRAYFORMULA(TRANSPOSE(SPLIT(A2:A7, " ")))
The following examples shows how to use each of these formulas in practice.
Example 1: Split Text into Rows (Space Delimiter)
Suppose we have the following cell in Google Sheets that contains several text values separated by spaces:
We can type the following formula into cell B2 to split the text in cell A2 into several rows:
=TRANSPOSE(SPLIT(A2, " "))
The following screenshot shows how to use this formula in practice:
Notice that the text in cell A2 has been split into rows.
Example 2: Split Text into Rows (Comma Delimiter)
If a cell instead contains several text values separated by commas, we can use the following formula to split the text in the cell into several rows:
=TRANSPOSE(SPLIT(A2, ","))
The following screenshot shows how to use this formula in practice:
Notice that the text in cell A2 has been split into rows.
Example 3: Split Multiple Cells with Text into Rows
Suppose we have a column of cells in Google Sheets that each contain several text values separated by spaces:
We can type the following formula into cell C2 to split the text in each of the cells in column A into rows:
=ARRAYFORMULA(TRANSPOSE(SPLIT(A2:A7, " ")))
The following screenshot shows how to use this formula in practice:
Notice that the text in each cell in column A has been split into rows.
Also notice that the formula works even though each cell in column A doesn’t have the same number of text values.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
Google Sheets: How to COUNTIF Not Equal to Text
Google Sheets: Check if Cell Contains Text from List
Google Sheets: Filter for Cells that Do Not Contain Text
