Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 good idea3


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!
doc split cell
 
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.

arrow blue right bubble 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:


arrow blue right bubble 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.


arrow blue right bubble 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.

Kutools for Excel, with more than 200 handy functions, makes your jobs more easier. 

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:
doc kutools 1

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:

doc kutools 2

3. Click Ok, and a dialog pops out to remind you select a cell to output split result.
doc kte 3

4. Click OK. Now you can see a cell has been converted to multiple rows.
doc kte 4

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.

arrow blue right bubble 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!
doc transpose cross to list
 
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 60 days.
 
 
 
 

Relative Articles:



Recommended Productivity Tools

Office Tab

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

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Tushar Bhalerao · 5 months ago
    this is really helpfull for me, :) Thanks a lot.
  • To post as a guest, your comment is unpublished.
  • To post as a guest, your comment is unpublished.
    Rajiv Kumar · 1 years ago
    C033047C033025C0125397C033023C033033

    I would like that above number split in different row & start with C0.
    I have excel & in this excel above number in row. I would like in different row.
  • To post as a guest, your comment is unpublished.
    Sadhik · 1 years ago
    I have a table like this & i want to split the data in such a way that the result is appeared as in the output format.
    Any help is appreciated.


    Excel file :
    Id Disabled Production Group Name of Work Step
    BHM_777-57-854-01-01/551_1 0 LEFT WING OPEN PANELS 551AB 551BB 551CB


    desired output format :

    BHM_777-57-854-01-01/551_1 0 LEFT WING OPEN ACCESS PANEL 551AB
    BHM_777-57-854-01-01/551_2 0 LEFT WING SIGN OFF OPEN ACCESS PANEL 551AB
    BHM_777-57-854-01-01/551_3 0 LEFT WING OPEN ACCESS PANEL 551BB
    BHM_777-57-854-01-01/551_4 0 LEFT WING SIGN OFF OPEN ACCESS PANEL 551BB
    BHM_777-57-854-01-01/551_5 0 LEFT WING OPEN ACCESS PANEL 551CB
    BHM_777-57-854-01-01/551_6 0 LEFT WING SIGN OFF OPEN ACCESS PANEL 551CB
  • To post as a guest, your comment is unpublished.
    Gokul Anand · 1 years ago
    Hi i wanna one macro code to split my data

    i have data like

    1
    2
    3
    4
    5

    1
    2
    3
    4
    5
    i wanna split this like
    12345
    12345

    Please if any one know ping me..

    Thanku