How to Split Text to Rows in Google Sheets (Easiest Way 2023)

In this article we will show you how to split text to rows Google Sheets. Simply follow our steps below and copy the example formula and you can split any text into rows in just a few clicks! 

Split Text Into Rows in Google Sheets

Google Sheets does not have a split text to rows feature. A workaround for this is to use the SPLIT function combined with the TRANSPOSE function. We explain exactly how to do this below:  


=TRANSPOSE(SPLIT(cell, delimiter))


=SPLIT: splits data to several cells depending on the delimiter set

Cell: the reference to the cell containing the string to split

Delimiter: the special character in the data to use as markers for splitting it to columns; they are closed in double quotes

=TRANSPOSE: converts columns to rows and rows to columns

The SPLIT function divides the input data to columns, while the TRANSPOSE function converts the columns to rows and rows to columns:

Transpose function example how it works

Finally, note that SPLIT function applies to only a single cell.

1. Identify cell and delimiter (separators)

Split text to rows google sheets, select cell

In this example, the string to split is in the cell A1 and the delimiters are the comma symbol:

Cell: A1

Delimiter “,”

2. Use formula =TRANSPOSE(SPLIT(cell, delimiter))

For this example, the formula becomes


We will add this formula to cell D1.

Split text to rows google sheets, add formulas

3. Press Enter.

The output becomes:

Split text to rows google sheets, output


Can you split more than one cell with text to rows?

Yes! Just modify the formula by enclosing it inside ARRAYFORMULA:


We have the following example:

Split text to rows google sheets, two cells containing text to split

The cells are A1 and A2. We can combine them into A1:A2 as the cell input to the formula:


The result becomes two cells split into two columns.

Split text to rows google sheets, two cells split to two columns

How to remove the #REF! error?

Split text to rows google sheets, #ref! error

This error happens when the output range overlaps with a cell with a stored value. To remove the error, just clean the cells of any stored value. 

How to remove the #VALUE! error? 

Split text to rows google sheets, #value! error

This happens when the input cell reference points to a blank cell. To remove the error, recheck the formula to see if it points to the correct cell with value to split.

Lido is a new spreadsheet built for automation. Trigger emails, slack messages, and more directly from a spreadsheet.