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

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:

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 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. This VBA does not support split cell based on the new line character.

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

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

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

Read More... Free Download... Purchase... 

---

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download... Purchase...