Sajid Ahmed
We are quite familiar with the Split Text to Columns tool in Google Sheets. This tool automatically splits texts into different columns. The SPLIT function also serves the same purpose. But sometimes we need to split texts into different rows instead of columns. So, in this article, I’ll show you 2 useful methods to split a cell into rows in Google Sheets with clear images and steps. I’ll also show how to split a cell horizontally in Google Sheets. The output will finally look like the image below.
A Sample of Practice Spreadsheet
You can download Google Sheets from here and practice very quickly.
2 Useful Methods to Split Cell into Rows in Google Sheets
Let’s get introduced to our dataset first. Here we have some products in Cell B5 of our dataset. The products are separated by using a delimiter comma. Now, we want to split the products in Cell B5 into separate rows. So, we’ll see 2 useful methods to split a cell into rows in Google Sheets by using this dataset.
1. Using Split Text to Columns and Transposed Commands
First of all, we can use the Split Text to Columns and Transposed commands to split a cell into rows in Google Sheets. The Split Text to Columns tool directly divides the values of a cell into different columns with respect to a delimiter. The delimiter can be some space, comma, semi-colon, period, or some custom sign. In our dataset, we have used commas as a delimiter. So, by using this tool we get the split values across different columns. But we want them across different rows of a single column. That’s why we’ll apply the Transposed command after using the Split Text to Columns tool. Now, we’ll see the methods for 2 conditions i.e. for single cell and for multiple cells.
1.1 For Single Cell
At this moment, I’ll show you the methods of splitting a single cell into different rows.
Steps:
- Firstly, select Cell B5 and copy it by pressing Ctrl+C together from your keyboard.
- Secondly, activate Cell B8.
- Then, press Ctrl+V from the keyboard to paste the values you copied earlier.
- Again, select Cell B8 and go to Data > Split Text to Columns.
- It’ll automatically split the products into different columns like Columns B to F serially.
- Now we have to bring them into different rows of a single column, Column B.
- So, select all the cells from Cell B8 to F8 and copy them by pressing Ctrl+C together.
- Next, activate Cell B8.
- Then, right-click on it with the mouse and go to Paste Special > Transposed.
- After that, the values will be transposed in Column B.
- Additionally, we’ll eliminate some of the remaining values since you may find them in Columns C to F.
- These values continue to exist because copies made in Google Sheets preserve the original values.
- Consequently, select all the cells from Cell C8 to F8 and remove them by pressing the Delete key from your keyboard.
- Finally, we’ll get the values split into 5 rows one by one serially in Column B.
Read More: How to Split Text to Columns Using Formula in Google Sheets
1.2 For Multiple Cells
Apart from the previous method we have a different dataset now. We have some products and their sales prices in 2 cells of Column C. These values are separated with a delimiter comma. We want to divide both cells into various rows of Columns B and C serially. So, we’ll see the method of splitting multiple cells into different rows below.
Steps:
- At first, press Ctrl+C from your keyboard. It’ll select Cells C5 and C6 for you to copy.
- Then, to paste the numbers you previously copied, activate Cell B9 and use the keyboard shortcut Ctrl+V.
- Next, you’ll see your pasted data in both Cells B9 and B10.
- After that, select both Cells B9 and B10.
- Consequently, go to Data > Split Text to Columns.
- It’ll automatically divide the products and sales data into two rows of distinct columns, serially arranged from Columns B to F.
- We now need to arrange them into several rows of two columns, Columns B and C.
- Therefore, pick all of the cells from Cell B9 to Cell F10 and then press Ctrl+C to copy them.
- Moreover, activate Cell B9, click it with the mouse’s right-click command, and select Paste Special > Transposed.
- Then, you’ll see the transposed values in Columns B and C.
- Next, we’ll also remove a few of the remaining values since you might discover them in Columns D to F.
- After that, press the Delete key on your keyboard to eliminate all the cells from Cell D9 to Cell F10 that you have chosen.
- In the end, we’ll see the values divided into 5 rows sequentially in both Columns B and C.
Read More: How to Split Cell by Comma in Google Sheets (2 Easy Methods)
Similar Readings
- How to Split String into Array in Google Sheets (3 Easy Methods)
- [Solved!] Split Text to Columns Is Not Working in Google Sheets
- How to Split Address in Google Sheets (3 Easy Methods)
2. Merging SPLIT and TRANSPOSE Functions
We can also merge the SPLIT and TRANSPOSE functions to split a cell into rows in Google Sheets. The SPLIT function splits the values of a cell into different columns. Then, the TRANSPOSE function transposed them across different rows of a single column. We get our results quickly by this method. Let’s see the process.
Steps:
- First of all, type the following formula in Cell B8–
=TRANSPOSE(SPLIT(B5,","))- Then, hit Enter to get the result at once.
Formula Breakdown
- SPLIT(B5,”,”)
Firstly, this function divides the values of Cell B5 into various columns according to the comma used as a delimiter.
- TRANSPOSE(SPLIT(B5,”,”))
Lastly, this function transposes them into various rows of a single column, Column B from different columns.
- Ultimately, in Column B, we’ll get the products divided into 5 rows in serial order.
Read More: How to Use QUERY with SPLIT Function in Google Sheets
How to Split a Cell Horizontally in Google Sheets
Sometimes we need to split a cell horizontally in Google Sheets. We can do this by using only one function, the SPLIT function. This function divides the values of a single cell into different columns horizontally with just a single click. You’ll find the process below.
Steps:
- Before all, select Cell D5.
- Then, insert the formula in it-
=SPLIT(B5,",")- Next, press Enter to get the outcome right now.
- Finally, we’ll get the products split into 5 columns horizontally through Columns D to G in serial order.
Read More: How to Split a Cell Diagonally in Google Sheets (3 Quick Ways)
Conclusion
That’s all for now. Thank you for reading this article. In this article, I have discussed 2 useful methods to split a cell into rows in Google Sheets. I have also discussed how to split a cell horizontally in Google Sheets. Please comment in the comment section if you have any queries about this article. You will also find different articles related to google sheets on our officewheel.com. Visit the site and explore more.
