Table of Contents
Splitting text into rows involves using a text editor such as Microsoft Word or a specialized text-splitting tool to separate the text into individual lines. Depending on the tool, this can be done manually by inserting line breaks or by using a delimiter such as a comma or tab to divide the text into separate rows. The end result will be a text document or spreadsheet with each row containing a single line of text.
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)
=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.