How to Split a Cell into Two Rows in Excel (3 ways)

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.

Sample Dataset

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

Using Text to Columns to Split a Cell into Two Rows

➤ 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

Using Text to Columns to Split a Cell into Two Rows

Here you can choose the Destination otherwise keep it as it is then click Finish.

Using Text to Columns to Split a Cell into Two Rows

➤ 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.

Using Text to Columns to Split a Cell into Two Rows

➤ 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.

Using Text to Columns to Split a Cell into Two Rows

➤ 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.

Using Text to Columns to Split a Cell into Two Rows

➤ 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.

=TRANSPOSE(D5)

Using Text to Columns with TRASPOSE to Split a Cell into Two Rows

➤ Here the selected value is transposed in cell C6.

Read More: How to Split One Cell into Two in Excel (5 Useful Methods)


Similar Readings


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

Using VBA to Split a Cell into Two Rows

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

Using VBA to Split a Cell into Two Rows

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.

Using VBA to Split a Cell into Two Rows

➤ 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.

Using VBA to Split a Cell into Two Rows

Finally, you will see the selected cell’s value is split into two rows.

Read More: Excel VBA: Split String into Cells (4 Useful Applications)


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

Using Power Query to Split a Cell into Two Rows

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.

Using Power Query to Split a Cell into Two Rows

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.

Using Power Query to Split a Cell into Two Rows

➤ 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.

Read More: How to Split Cells in Excel (The Ultimate Guide)


Practice Section

I’ve provided an extra practice sheet in the worksheet so that you can practice these explained methods.

Practice Sheet to Split a Cell into Two Rows

Conclusion

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.


Related Articles

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo