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

Get FREE Advanced Excel Exercises with Solutions!

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 ExcelIn the picture below, there is an overview of how the result will be after splitting a cell into two rows in Excel.

The Overview Image of How to Split 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).

Dataset of How to Split a Cell into Two Rows in Excel

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.

Selecting Text to Columns Command from Data tab

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

Clicking Delimited in text to Columns Wizard

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

Choosing Delimeter in Text to Columns Wizard

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

Clicking Finish in Convert Text to Column Wizard

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

Splitted Cell into two columns

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

Cut & pasting D5 to C6

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

Result after cut and pasting all the cells


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.

<span style="font-size: 14pt;"><strong>=TRANSPOSE(D5)</strong></span>

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

Using Transpose Function to copy data from another cell

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

Results after splitting cell into two rows

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.

Opening the VBA application window from Developer tab

  • It will open a new window of Microsoft Visual Basic for Applications.
  • Now click on Insert >> Module to open a new module.

Inserting New Module from VBA Editor

  • A new Module will open.

New Empty Module in VBA Editor

  • 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

VBA Code

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

Opening VBA Macro Dialogue Box to Run Macro

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

Choosing the cell to be split

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

Choosing the range where the cell will be split into.

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

Result after splitting cell into two rows.

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

Final result of splitting cell into two rows using vba code

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.

Dataset for using Power Query to split cells into two rows

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.

Selecting data source From Table/Range

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

Selecting the Data source

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

The Power Query Window

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

Splitting Column using delimeter.

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

Choosing Delimiter and split into Rows from the Split Column by delimiter window.

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

Results After splitting cells into two rows

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

Clicking on Close and Load to option.

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

Final result after using Power Query to Split cells into two rows.

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

Shamima Sultana
Shamima Sultana

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in 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.

2 Comments
  1. Not helpful.
    The following steps have nothing to do with the picture at the beginning of the article.

    • Hello, Xzavier!

      For better understanding, we updated the article. Kindly check it now it would be helpful.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo