Google Sheets: How to Split Text into Rows


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:

Google Sheets split text into rows

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:

Google Sheets split text in several cells into rows

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

Leave a Reply

Your email address will not be published. Required fields are marked *