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
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!
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
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)
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.
Kutools for Excel: A handy Excel add-in tool,makes your jobs more easier and efficiency.
300+ powerful and easy-to-used functions,110,000+ Excel users'choice
full-featured free trial in 30 days, no credit card required
dozens of one-click functions to shrink your working time on solving complex problems
includes functions to batch converion,deletion,combining sheets/cells,exproting, ect, free try 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.
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.
Hi, Sunil Graphics, if there is no trailing space after the comma and dot, you can do as these:
1. Select the cell that contains data, click Data > Text to Column, in the popping text to column dialog, check delimited > next > check space > click Finish, now the data has been split into columns based on space, like p.umesh,125 | ranesh,52 | sunil,478 |...
2. Then select these column data, press Ctrl + C to copy them, and select a another cell, right click to select Transpose in the paste options in the context menu. Now the data shows as
3. Select the transpose data, use the Text to Column function again, this time, check delimited > next > comma > finish. Then it done.
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
in cell a1 i have date in cell b1 period and in c1 i have amount now i want that cell1 date ok, but cell b1 is period that 01.01.16 to 31.01.16 now i want that cell b1 to split in two column so that it look like this ::
A1 B1 C1 Date Period Amount 01.01.16 01.01.16 31.01.16 15000
I have a file with thousands of rows and i want one of the column to be divided into multiple rows and columns without disturbing its mapping with other columns.
Column A Column B Column C Team Name Jeopardy Rules Res Product and Sales Inquiry Equipment Inquiry or Sale eMTA Inquiry QC/ON "Open for 72 hours - MGRSUPERCC (ON)/SACSUP (QC) Open for 96 hours - MGRSUPERCC (ON)/SACSUP (QC) In Progress for 120 hours - MGRSUPERCC (ON)/SACSUP (QC) In Progress for 150 hours - MGRSUPERCC (ON)/SACSUP (QC) In Progress for 200 hours - MGRSUPERCC (ON)/SACSUP (QC) Resolved for 168 hours - Assigned Agent/MGRSUPERCC (ON)/SACSUP (QC)"
In the above example, entire content is in last cell. Row 2 column E
Column A Column B Column C Column D Hours Status ON QC Res Product and Sales Inquiry Equipment Inquiry or Sale eMTA Inquiry QC/ON 72 Open MGRSUPERCC (ON) SACSUP (QC) Res Product and Sales Inquiry Equipment Inquiry or Sale eMTA Inquiry QC/ON 96 Open MGRSUPERCC (ON) SACSUP (QC) Res Product and Sales Inquiry Equipment Inquiry or Sale eMTA Inquiry QC/ON 120 In Progress MGRSUPERCC (ON) SACSUP (QC) Res Product and Sales Inquiry Equipment Inquiry or Sale eMTA Inquiry QC/ON 150 In Progress MGRSUPERCC (ON) SACSUP (QC) Res Product and Sales Inquiry Equipment Inquiry or Sale eMTA Inquiry QC/ON 200 In Progress MGRSUPERCC (ON) SACSUP (QC) Res Product and Sales Inquiry Equipment Inquiry or Sale eMTA Inquiry QC/ON 168 Resolved MGRSUPERCC (ON) SACSUP (QC)
I have my dataset B1311740 firstname.lastname@example.org email@example.com B1528120 firstname.lastname@example.org B1610861 email@example.com firstname.lastname@example.org I want the data as below B1311740 email@example.com B1311740 firstname.lastname@example.org B1528120 email@example.com B1610861 firstname.lastname@example.org B1610861 email@example.com
Step 01:Insert 2 column on the Right. Step 02:Fill-In B column with 1,2,3..... Step 03:Sort on Column E. Step 04:in the formula in column A1. =If(e1"",b1+0.5,0) Step 05:copy the formula to the rest of A column Step 06:Copy the contents of A column to the unused space in B column (at the end of Column B). Step 07:Clear the contents in Column A. Step 08:Sort on Column B. Step 09:Delete those line that has 0 in column B. Step 10:In E1, insert a blank space, (with the option shift cell down). Step 11:Sort on Column D. Step 12:Delete the empty cells in Column D, (with the option shift cell left). Step 13:Sort on Column B again. Step 14:In A1, copy c1 to A1. Step 15:In A2, add the formula =IF(C2="",A1,C2) Step 16:Copy the formula to the rest of A1. Step 17:Copy the contents of Column A, and paste (value) onto Column C. Step 18:Delete column A & B.
Great tutorial. Do you happen to know, how can you automatize this? I mean if you have multiple cells you want to transpose one below the other? (I am already having the needed number of empty rows in between them, so there is enough space to transpose, just I have more the 2500 rows, and would like to do it one by one.)