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.
- Mark Comma as we want to split the data into columns separated by comma.
- Then press Next.
- In the last step, mark General.
- Finally, press Finish.
Now see that the data are split into multiple columns along row 5. Now we’ll place them 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.
- 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.
- Then select the Macro name as specified in the codes.
- Press Run.
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.
- At this moment, just press OK.
And soon after, a Power Query window will open up.
- Click on the header.
- Later, click as follows: Split Column > By Delimiter.
Consequently, another dialog box will open up.
- Select Comma from the Select or enter delimiter box.
- Then from the Advanced options, mark Rows.
- Press OK.
Now have a look at the data are split into 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.
- Then mark Delimited and press Next.
- In this step, mark Comma and again press Next.
- In the last step, Mark General.
- Finally, press Finish.
Now the data are split into Columns B and C.
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.
