.png)
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:
Syntax
=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.
1. Identify cell and delimiter (separators)
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
=TRANSPOSE(SPLIT(A1,”,”))
We will add this formula to cell D1.
3. Press Enter.
The output becomes:
FAQs
Can you split more than one cell with text to rows?
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.
How to remove the #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?
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.
