How to convert one cell to multiple cells/rows in Excel?

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

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 future 60 days free trial!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.

Convert one cell to multiple cells/rows with Text to Column and Paste Special in Excel

1. 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:

Convert one cell to multiple cells/rows with VBA

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

Convert one cell to multiple columns/rows with Kutools for Excel

If 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 > Text > 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 columns

Quickly 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 60-day full-featured free trial!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.

Relative Articles:

• Transpose Range utility: Easily convert multiple rows to columns and rows in Excel
• Unit Converter tool: Quickly convert decimal number to binary/octal/hex number or vice versa

Recommended Productivity Tools

Office Tab

Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel