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!
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))
Where
=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:
Finally, note that SPLIT function applies to only a single cell.
In this example, the string to split is in the cell A1 and the delimiters are the comma symbol:
Cell: A1
Delimiter “,”
For this example, the formula becomes
=TRANSPOSE(SPLIT(A1,”,”))
We will add this formula to cell D1.
The output becomes:
Yes! Just modify the formula by enclosing it inside ARRAYFORMULA:
=ARRAYFORMULA(TRANSPOSE(SPLIT(cell, delimiter)))
We have the following example:
The cells are A1 and A2. We can combine them into A1:A2 as the cell input to the formula:
=ARRAYFORMULA(TRANSPOSE(SPLIT(A1:A2, ",")))
The result becomes two cells split into two columns.
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.
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.
