In this tutorial, you will learn how to split text to rows in Google Sheets.
How to Split Text to Rows in Google Sheets
Have you ever encountered text values that you want to split into multiple cells? Google Sheets provides several methods to split a single text value into multiple columns. However, we need to find a workaround if we want to output our split values as multiple rows instead.
For example, you may have a text value like “Yes, No, Maybe” that you want to display as a vertical list.
In this guide we will provide two different methods that you can use to split text to rows in Google Sheets. The first method will involve using the Split to columns and Paste Special feature. Users who want a more dynamic method can use the next method which uses a custom Google Sheets formula.
How to Convert Text to Multiple Rows in Google Sheets
Here’s how to convert text to multiple rows in Google Sheets.
Step 1
First, select the cell that includes the text you want to split into rows. It is important for the text itself to have a consistent separator for each value you want to split into its own row.
In the example seen above, we want to split a list of colors into multiple rows. The list uses a comma as a separator for each value. Other common separators include whitespace characters and the semi-colon character.
Step 2
Next, select the option Data > Split text to columns.
Step 3
Google Sheets will divide the selected text into multiple columns.
Step 4
Since we want our output to be a range of multiple rows, we can use the Paste Special option to modify our output range.
First, copy the range by clicking on Ctrl + C or Cmd + C. Next, right-click on an empty cell and select Paste special > Transposed.
Step 5
The text should now be transposed and appear in separate rows rather than separate columns.
One disadvantage of this method, however, is that we can no longer recover the original text. Luckily, there is another solution that allows us to split text to rows using a simple Google Sheets formula.
Step 6
We can use the TRANSPOSE and SPLIT functions together to split text to rows.
The SPLIT function takes a text value and a delimiter and returns an array with the resulting split text. For example, the formula SPLIT(“a;b;c”, ”;”) will return the array [“a”,”b”,”c”].
However, since the output of SPLIT falls on the same column, we will have to use the TRANSPOSE function to convert the output to a vertically-arranged array.
In the example above, we used the formula =TRANSPOSE(SPLIT(A11,”,”) to split the comma-separated text value in cell A11 to multiple rows.
Step 7
We can use the ARRAYFORMULA to make the method in the previous step work for multiple cells.
In the example above, we were able to split the text in A11 and A12 into multiple rows using a single formula.
Summary
This guide should be everything you need to split text to rows in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.