Sometimes, it is difficult to view all the data if there are lots of data in a cell in Excel. In this case, converting all the data in this cell to multiple cells or rows may make it much easier and clearer to view long content as shown as below. In Excel, you can get it done through below methods.
Convert one cell to multiple cells/rows with Text to Column and Paste Special in Excel
Convert one cell to multiple cells/rows with VBA
Convert one cell to multiple columns/rows with Kutools for Excel 
Convert one cell to multiple cells/rows with Text to Column and Paste Special in Excel1. Select the cell you want to convert its data, and click Data > Text to Column, see screenshot:
2. In the pop-up dialog, check Delimited check box, and click Next. See screenshot:
3. Check Comma check box only in the dialog, and click Finish. See screenshot:
4. Then you can see the cell data has been split into multiple columns. And now select these cells and right click to click Copy from the context menu to copy them. See screenshot:
5. Select a blank cell you want and right click to select Transpose(T), then you can see the data has been converted into multiple rows. See screenshot:
If you are using Microsoft Excel 2007, click the Home > Paste > Transpose to paste the split data.
You can see the result as shown:
Quickly Split one cell into columns or rows based on delimiter |
| In Excel, to split a cell into columns is tedious with the Wizard step by step. But with Kutools for Excel's Split Cells utility, you can: 1,convert one cell into columns or rows based on delimiter; 2,convert string into text and number; 3,convert string based on specific width, with clicks. Click for full-featured 30 days free trial! |
 |
| Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
Convert one cell to multiple cells/rows with VBAIf you think the above method is a little tedious, you can use a VBA to get it done.
1. Hold ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window.
2. Click Insert > Module, and copy the VBA into the module.
VBA: Convert one cell to multiple rows
Sub TransposeRange()
'Updateby20140312
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection.Range("A1")
Set InputRng = Application.InputBox("Range(single cell) :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Arr = VBA.Split(InputRng.Range("A1").Value, ",")
OutRng.Resize(UBound(Arr) - LBound(Arr) + 1).Value = Application.Transpose(Arr)
End Sub
3. Click Run to run the VBA code, and a KutoolsforExcel dialog pops up for you to select a single cell you want to convert its data. See screenshot:
4. Click OK, and in another popup dialog select a cell to output the result. See screenshot:
You can see the result:
Tip: In the above VBA code, you can change the separator "," you want to separate the data by. This VBA does not support split cell based on the new line character.
Convert one cell to multiple columns/rows with Kutools for ExcelIf you have Kutools for Excel -- a handy tool installed, you can use its Split Cells fuction to quickly split a single cell to multiple columns or rows based on a delimiter.
After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)
1. Select the cell you want to split to rows, and click Kutools > Merge & Split > Split Cells. See screenshot:
2. Then in the Split Cells dialog, check Split to Rows option under Type section, and check Other option under Specify a Separator section, then enter the delimiter you want to split cell based on in the textbox. See screenshot:
3. Click Ok, and a dialog pops out to remind you select a cell to output split result.
4. Click OK. Now you can see a cell has been converted to multiple rows.
Tip: If you want to convert a single cell to columns based on specific delimiter, check Split to Columns option in dialog, and enter the delimiter into Other textbox.
Convert one cell into multiple rows or columnsQuickly transpose Cross table to list or vice versa |
| While you receiving a sheet with cross table which you need to convert to list table, how can you quickly deal with it? With Kutools fro Excel's Transpose Table Dimensions utility, you can convert cross dimensions table to three demensions table or vice versa as quickly as you can. Click for 30-day full-featured free trial! |
 |
| Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
