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

If in a large dataset, multiple pieces of information are compacted in one single cell, then it is hard to find and look up data or perform any task. In this article, I’m going to explain how to split a cell into two rows in Excel. In the picture below, there is an overview of how the result will be after splitting a cell into two rows in Excel.

In this section, I will describe 3 easy methods for splitting a single 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 Author and Book Name. Here, there are some cells where multiple books’ names are in one cell (C5, C7, C10).

Our goal is to split the names of books in cells C5, C7, and C10 into two rows (C5:C6, C7:C8, and C10: C11 respectively). Let’s explore the methods one by one to accomplish our goal.

1. Using Text to Columns Feature to Split a Cell into Two Rows in Excel

You can use the Text to Columns from the Ribbon to split a cell into rows. This feature converts a piece of text into multiple columns. But we want to convert a piece of text to convert into rows. Hence, we need to manually convert the extracted columns into rows. To know more, follow the steps below.

Steps:

• First, select the cell that you want to split. Here, I selected cell C5.
• Then, open the Data tab.
• Now, from Data Tools, select the Text to Columns option.

• As a result, a dialog box named Convert Text to Columns Wizard will pop up.
• From there, select the file type Delimited and click Next.

• Now, from step 2, select the Delimiters by which your text will be separated. In my case, the authors’ names are separated by a comma (,). Hence, I selected comma (,).
• After that, click Next.

• Here in step 3, if you want to place the separated texts in a different location, you can choose the Destination. On the other hand, if you want to replace the text, keep the field as it is.
• Finally, click Finish.

• As a result, you will see that the values are split into two columns (C5 & D5).

• But our target is to split these values into two rows. Unfortunately, we need to do this manually.
• To convert the split columns into rows, we can take two different approaches. There are two conventional ways to flip columns to rows. They are the Cut Paste Options and the TRANSPOSE function.

Read More: Excel VBA: Split String into Rows (6 Ideal Examples)

1.1. Using Cut-Paste Options

Here, we can simply cut cell D6 and paste it into cell C7. To do that, follow the steps below.

Steps:

• Firstly, select cell D5.
• Now, press CTRL+ X to cut the cell.
• Then, go to cell D6.
• Now, press CTRL+V to paste the text.

• In this way, we can also split cells C7 and C10 and obtain the desired result.

1.2. Using TRANSPOSE Function

You can also use the TRANSPOSE function to split a cell into rows after using Text to Columns.  To do that, follow the steps below.

Steps:

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

• Now, click Enter. As a result, you will see the following result.

• Now, we can apply the same thing in cells C7 and C10. Finally, we get our desired result.

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

2. Using Excel VBA to Split a Cell into Two Rows

You can use a VBA code to split a cell into two rows. To know more, follow the steps below.

Steps:

• First, open the Developer tab.
• Then select Visual Basic.

• It will open a new window of Microsoft Visual Basic for Applications.
• Now click on Insert >> Module to open a new 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("Select the cell to be split(single cell) :", ExcelTitleId, InputRng.Address, Type:=8)
Set OutputRng = Application.InputBox("Select the range of rows (a range):", ExcelTitleId, Type:=8)
Arr = VBA.Split(InputRng.Range("A1").Value, ",")
OutputRng.Resize(UBound(Arr) - LBound(Arr) + 1).Value = Application.Transpose(Arr)
End Sub

• Now, save the code and go back to the worksheet.
• Then, select the cell that you want to split into rows. I selected cell C5.
• Now, open the Macro dialogue box by clicking ALT+F8. 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.

• Similarly, we can follow the same process to split cells C7 and C10 into two rows C7:C8, and C10: C11

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

3. Using Power Query to Split Cell into Two Rows in Excel

You can also use the Power Query feature to split a cell into rows. This method is ideal to use when the dataset has no empty cells like this below.

Now to split the cells C5, C6 & C8 using the feature, follow the steps below.

Steps:

• Firstly, select the entire dataset.
• Secondly, open the Data tab.
• Thirdly, select From Table/Range option from the ribbon.

• Now, a dialog box will pop up showing the selection. From here, select My table has headers. Then, click OK.

• Consequently, the Power Query Editor window will pop up.

• From there, select any cell that we want to split into two rows.
• Then, go to the Home tab.
• Now, click on Split Column >>By Delimiter.

• A dialog box will pop up. From there select the Delimiter comma(,) then click on the Advanced Options. From there, select Rows.
• Finally, click OK.

• As a result, you will see that all the cells that contain a delimiter(comma) have been split into multiple rows. Moreover, the adjacent cells in the left column were also duplicated.

• Now, click the Close & Load option from the ribbon to load the data in another sheet in the workbook.

• As a result, the table will be loaded into a separate sheet.
• After some formatting, the final result will be like this below.

In this way, we can easily split a cell into two rows in Excel.

Read More: Excel Formula to Split String by Comma (5 Examples)

Things to Remember

• When your values are not related to the adjacent cell, or you just have only one column then Power Query will work perfectly.

Download Practice Workbook

Download this workbook to exercise while you are reading this article.

Conclusion

In this article, I’ve explained several ways to split a cell into two rows in Excel. Hopefully, 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

• 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 by Number of Characters (2 Easy Methods)
• How to Split Cell by Delimiter Using Excel Formula (8 Ways)
• Excel Formula to Split: 8 Examples
• How to Split a Single Cell in Half in Excel