How to Split Data from One Cell into Multiple Rows in Excel (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

We can easily split data from one cell to multiple cells by copying but that is not feasible always, especially for a large dataset. To do that easily and smartly, Excel has some amazing features. I’ll introduce you to those 3 smart ways to split data from one cell into multiple rows in Excel with sharp demonstrations.


How to Split Data from One Cell into Multiple Rows in Excel: 3 Methods

1. Apply Text to Columns Wizard to Split Data from One Cell into Multiple Rows

I have placed 5 products’ names in Cell B5. Now I’ll split them into multiple rows along cells B8:B12 using the Text to Columns Wizard.

Steps:

  • Select Cell B5.
  • Then click as follows: Data > Text to Columns.

A 3-step dialog box will open up.

  • Mark Delimited and press Next in the first step.

Apply Text to Columns Wizard to Split Data from One Cell into Multiple Rows

Apply Text to Columns Wizard to Split Data from One Cell into Multiple Rows

  • In the last step, mark General.
  • Finally, press Finish.

Apply Text to Columns Wizard to Split Data from One Cell into Multiple Rows

Now see that the data are split into multiple columns along row 5. Now we’ll place them into multiple rows.

Apply Text to Columns Wizard to Split Data from One Cell into Multiple Rows

  • Select the cells B5:F5 and copy them.
  • Then right-click your mouse on the first row of the range where you want to paste them.
  • Select Transpose from the Paste Options.

Then you will get the split items into multiple rows.

Read More: How to Split Comma Separated Values into Rows or Columns in Excel


2. Embed VBA Macros to Split Data from One Cell into Multiple Rows in Excel

If you like to work with VBA in Excel then you can easily do the task using VBA Macros. It’s pretty quick compared to the previous methods.

Steps:

  • Right-click your mouse on the sheet title.
  • Select View Code from the Context menu.

Embed VBA Macros to Break Data from One Cell into Multiple Rows in Excel

  • After the VBA window appears, write the following codes in it-
Sub Split_OneCell()
Dim Data As Range
Dim InputData As Range, Output_Rng As Range
BoxTitle = "Split One Cell into Multiple Rows"
Set InputData = Application.Selection.Range("A1")
Set InputData = Application.InputBox("Input Single Cell :", BoxTitle, InputData.Address, Type:=8)
Set Output_Rng = Application.InputBox("Destination:", BoxTitle, Type:=8)
Arr = VBA.Split(InputData.Range("A1").Value, ",")
Output_Rng.Resize(UBound(Arr) - LBound(Arr) + 1).Value = Application.Transpose(Arr)
End Sub
  • Later, press the Run icon to run the codes.

Embed VBA Macros to Break Data from One Cell into Multiple Rows in Excel

  • Then select the Macro name as specified in the codes.
  • Press Run.

Embed VBA Macros to Break Data from One Cell into Multiple Rows in Excel

Soon after, you will get a dialog box to select the source cell.

  • Select Cell B5 and press OK.

Another dialog box will open up.

  • Now select the first cell of the destination cells.
  • Finally, press OK.

Now we are done.

Read More: Excel Macro to Split Data into Multiple Files


3. Use Excel Power Query to Divide Data from One Cell into Multiple Rows

Excel Power Query is another useful tool to split data from one cell into multiple rows. Let’s see how to apply it.

Steps:

  • Select the one cell including the header.
  • Then click: Data > From Table/Range.

Use Excel Power Query to Divide Data from One Cell into Multiple Rows

  • At this moment, just press OK.

And soon after, a Power Query window will open up.

Use Excel Power Query to Divide Data from One Cell into Multiple Rows

  • Click on the header.
  • Later, click as follows: Split Column > By Delimiter.

Consequently, another dialog box will open up.

Use Excel Power Query to Divide Data from One Cell into Multiple Rows

  • Select Comma from the Select or enter delimiter box.
  • Then from the Advanced options, mark Rows.
  • Press OK.

Use Excel Power Query to Divide Data from One Cell into Multiple Rows

Now have a look at the data are split into rows.

Use Excel Power Query to Divide Data from One Cell into Multiple Rows

  • After that, click Close & Load > Close & Load To from the Home tab.

  • Then after appearing the new dialog box, mark Table and New worksheet.
  • Finally, press OK.

Soon after, you will get a new worksheet with the split data into multiple rows.


How to Split Multiple Cells into Rows

Not only for one cell but also we can split multiple cells into rows using the Text to Columns Wizard. In this section, we will learn how to do that.

Steps:

  • First, select multiple cells.
  • Then click as follows: Data > Text to Columns.

How to Split Multiple Cells into Rows

  • Then mark Delimited and press Next.

How to Split Multiple Cells into Rows

  • In this step, mark Comma and again press Next.

How to Split Multiple Cells into Rows

  • In the last step, Mark General.
  • Finally, press Finish.

How to Split Multiple Cells into Rows

Now the data are split into Columns B and C.

How to Split Multiple Cells into Rows

Now we’ll copy and transpose them.

  • Select the data of the first split row and copy them.
  • Then in the first destination row, right-click your mouse and paste as Transpose.

  • Do the same thing for the data of the second split row.

Then you will get the output like the image below.


Download Practice Workbook


Conclusion

I hope the procedures described above will be good enough to split data from one cell into multiple rows in Excel. Feel free to ask any question in the comment section and please give me feedback.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo