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

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.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


3 Ways to Split Data from One Cell into Multiple Rows in Excel

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

  • Mark Comma as my data is separated using commas.
  • Then press Next.

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 items are split 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 a Cell into Multiple Rows (With Easy Steps)


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

Read More: How to Split Data in One Excel Cell into Multiple Columns (5 Methods)


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

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo