In this article:

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

May 8, 2024

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.

If you enjoyed this article, you might also like our article on how to split text in Google Sheets or our article on how to add crypto prices in Google Sheets. 

If you want to learn how to add reminders in Google Sheets, we also suggest checking out our detailed guide. 

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->