If in a large dataset multiple information are compacted in one cell, then it is hard to find and lookup data to view or to do any task. In this article, I’m going to explain how to split a cell into two rows in Excel.
Just to make the explanation more visible I’m using a sample dataset of book information. Here I’ve taken two columns these are Book Name and Author. Here, there are some cells where multiple authors’ names are in one cell.
Download to Practice
Ways to Split a Cell into Two Rows in Excel
1. Using Text to Columns to Split a Cell into Two Rows
You can use the Text to Columns from the Ribbon to split a cell into rows.
Let’s see the procedure.
First, select the cell that you want to split. Here, I selected the C5 cell.
Then, open the Data tab >> from Data Tools >> select Text to Columns
➤ A dialog box will pop up. From there select the file type Delimited and click Next.
➤ Now select the Delimiters your value has.
➤ I selected comma (,)
➤ Click Next
Here you can choose the Destination otherwise keep it as it is then click Finish.
➤ Here you will see the values are split into columns, but I want to split these values into two rows.
There are two conventional ways to flip columns to rows. They are the Paste Options and the TRANSPOSE function.
I. Paste Options
Now, to split the column values into rows, first select the cells.
You can use either Cut or Copy option.
➤ Now right click on the mouse then select Copy (you can use Cut as well).
➤ Select the cell where you want to place the value.
➤ I selected cell C6
➤ Again right click on the mouse then select Paste Transpose from Paste Options.
➤ Now you will find the selected value in the selected row.
II. TRANSPOSE function
You can also use the TRANSPOSE function to split a cell into rows after using Text to Columns.
➤ First, select the cell to place your value. I selected cell C6
Then, type the following formula in the selected cell or into the Formula Bar.
➤ Here the selected value is transposed in cell C6.
- Excel Split Cell by Delimiter Formula
- How to split a single cell in half in Excel (diagonally & horizontally)
- Excel Formula to Split: 8 Examples
- How to Make Two Lines in One Cell in Excel (4 Methods)
2. Using VBA to Split a Cell into Two Rows
You can use the VBA to split a cell into two rows.
➤ Open the Developer tab >> then select Visual Basic
It will open a new window of Microsoft Visual Basic for Applications.
➤From Insert >> select Module.
➤A new Module will open.
Now, write the code in the Module.
Sub Split_Cell_into_Rows() Dim rng As Range Dim InputRng As Range, OutputRng As Range ExcelTitleId = "Split Cell into Rows" Set InputRng = Application.Selection.Range("A1") Set InputRng = Application.InputBox("Range(single cell) :", ExcelTitleId, InputRng.Address, Type:=8) Set OutputRng = Application.InputBox("Output to (single cell):", ExcelTitleId, Type:=8) Arr = VBA.Split(InputRng.Range("A1").Value, ",") OutputRng.Resize(UBound(Arr) - LBound(Arr) + 1).Value = Application.Transpose(Arr) End Sub
➤Save the code and go back to the worksheet.
➤Now, select the cell that you want to split into rows. I selected cell C6
➤ Open the View tab >> from Macros >> select View Macro
➤ A dialog box will pop up. From there select the Macro to Run.
➤ Then a dialog box will pop up named Split Cell into Rows. You can select the cell first or you can select the range from the popped-up dialog box.
Now, in the Output to select the range where you want to place the split values of a cell.
➤ I selected the range C5:C6.
Finally, you will see the selected cell’s value is split into two rows.
3. Using Power Query
You also can use the Power Query to split a cell into rows.
➤ First, select the cell range.
➤Open the Data tab >> then select From Table/Range
Now, a dialog box will pop up showing the selection then select My table has headers. Then, click OK.
➤ Here, a new window will pop.
From there, select the cell for splitting it into rows.
Open Home tab >> from Split Column >> select By Delimiter
A dialog box will pop up. From there select the Delimiter comma(,) then select Rows from the Advanced Options. From Quote Character select None.
Finally, click OK.
➤ In the end, you will see that the selected cell is split into two rows.
But there is a disadvantage it splits the values by copying the adjacent cell value. To rectify this, you can remove the extra copied values then copy the split result into your desired rows.
When your values are not related to the adjacent cell, or you just have only one column then Power Query will work perfectly.
I’ve provided an extra practice sheet in the worksheet so that you can practice these explained methods.
In this article, I’ve explained several ways to split a cell into two rows in Excel. These methods will be useful for you whenever you want to split a cell into two rows. In case you have any confusion or question regarding these methods you may comment down below.
- Excel Formula to Split String by Comma (5 Examples)
- VBA to Split String into Multiple Columns in Excel (2 Ways)
- Excel VBA: Split String by Character (6 Useful Examples)
- How to Split Cells in Excel (5 Easy Tricks)
- Excel VBA: Split String into Rows (6 Ideal Examples)
- Excel VBA: Split String by Number of Characters (2 Easy Methods)