How do I copy a cell value to multiple cells in Excel?
1. Hold ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window. Show 2. Click Insert > Module, and copy the VBA into the module. VBA: Convert one cell to multiple rows
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. Kutools for Excel: A handy Excel add-in tool,makes your jobs more easier and efficiency.
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 columns300 30 Quickly transpose Cross table to list or vice versaWhile 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 for30 -day full-featured free trial! Kutools for Excel: with more than300 handy Excel add-ins, free to try with no limitation in30 days.Relative Articles:
The Best Office Productivity ToolsKutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Read More... Free Download... Purchase... Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
Read More... Free Download... Purchase...
Oldest First Sort comments by Oldest First Newest First Comments (37) No ratings yet. Be the first to rate! J. Felix about 8 years ago #12496 This comment was minimized by the moderator on the site 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.) Reply 0 0 Nizam about 7 years ago #14048 This comment was minimized by the moderator on the site I tried it, it works for small and simple data but it doesnt work with my data, can you explain what can i do what that data ? the formate is like names: comments: Dates: name1,name2,name3 comment1,coment2,comnt3 2015:3:20:10,2015:3:21:10 I Want to have name1, its comment1 and date1 in one row, and 2nd name, 2nd commnt, and 2date in 2nd row and so on. can you help me explain how to do this ? thanks Reply 0 0 Melissa about 7 years ago #14162 This comment was minimized by the moderator on the site The row or field I want to break out into multiple rows is one complete number and I need every 8 numbers in a new row. They are not separated by commas. How can this be done? Reply 0 0 Shiva about 7 years ago #14197 This comment was minimized by the moderator on the site This is my sheet. aaaaa 111111 bbbbbb 2222222 cccccccc 33333333 ddddddd 44444444 And i need to achieve like this. aaaaa 111111 bbbbbb 2222222 cccccccc 33333333 ddddddd 44444444 Reply 0 0 DavidNavsix about 7 years ago #14800 This comment was minimized by the moderator on the site Try this way, 1. insert a blank column in before the A column, and number the line 1,2,1,2....you will see: 1 aaaaa 2 11111 1 bbbbbb 2 2222222 1 cccccccc 2 33333333 1 ddddddd 2 44444444 2. Column the entire column b to column c, you get: 1 aaaaa aaaaa 2 11111 11111 1 bbbbbb bbbbbb 2 2222222 2222222 1 cccccccc cccccccc 2 33333333 33333333 1 ddddddd ddddddd 2 44444444 44444444 3. Delete the first cell (shift cell up) in column c, you get: 1 aaaaa 11111 2 11111 bbbbbb 1 bbbbbb 2222222 2 2222222 cccccccc 1 cccccccc 33333333 2 33333333 ddddddd 1 ddddddd 44444444 2 44444444 4. Now, sort column a, you will get: 1 aaaaa 11111 1 bbbbbb 2222222 1 cccccccc 33333333 1 ddddddd 44444444 2 11111 bbbbbb 2 2222222 cccccccc 2 33333333 ddddddd 2 44444444 Delete all the line 2, and column A, you get what you want: aaaaa 11111 bbbbbb 2222222 cccccccc 33333333 ddddddd 44444444 Reply 0 0 Pallavi about 7 years ago #14835 This comment was minimized by the moderator on the site I have Data in sheet in below format in a cell 1. Login to the Application 2. Enter valid credentials 3. Click on Submit Expected: Above three steps should get split in 3 rows Row 1 ----> 1. Login to the Application Row 2 ----> 2. Enter valid credentials Row 3 ----> 3. Click on Submit Please let me know is this possible in excel Reply 0 0 Daniel about 7 years ago #14920 This comment was minimized by the moderator on the site Thanx very helpful...atleast with this site i will be a genius soon Reply 0 0 sivaranjani about 7 years ago #15110 This comment was minimized by the moderator on the site I have my dataset B1311740 [email protected] [email protected] B1528120 [email protected] B1610861 [email protected] [email protected] I want the data as below B1311740 [email protected] B1311740 [email protected] B1528120 [email protected] B1610861 [email protected] B1610861 [email protected] Reply 0 0 DavidNavsix sivaranjani about 7 years ago #15111 This comment was minimized by the moderator on the site 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. Reply 0 0 dan about 6 years ago #15928 This comment was minimized by the moderator on the site I have a cell with multiple line breaks that I want to preserve and reserve into separate rows. For example: A B C D E Can I maintain the vertical spacing distributed as separate rows? Kutools seems to condense them into: A B C D E Reply 0 0 FATIN about 6 years ago #17009 This comment was minimized by the moderator on the site This is my sheet. aaaaa 111111 bbbbbb 2222222 cccccccc 33333333 ddddddd 44444444 And i need to achieve like this. aaaaa 111111 bbbbbb 2222222 cccccccc 33333333 ddddddd 44444444 Reply 0 0 Sachin Patil about 6 years ago #17105 This comment was minimized by the moderator on the site Hi, My data is like in cell A1= Today is My Last Day in School in cell A2 = Tomorrow is My First Day in Office Expected: B1 Today B2 B3 My B4 Last B5 Day and so on.... Data in row A should be in Row B such that, after every 72 characters, the data should move to the cell below it. (B1 should have 72 chars, then 72 remaining in B2 and so onn) Reply 0 0 Delicate about 6 years ago #17312 This comment was minimized by the moderator on the site I need to get from this All in one cell: aaaaaa bbbbbb ccccccc dddddd To all separated into individual cells without adding commas. (Text to columns button is not helping unless they are all on one line and commas are added.) Thanks! Reply 0 0 Rajeev Kumar about 6 years ago #17744 This comment was minimized by the moderator on the site how to split a value in different columns like my value is 30122016 i want to to do it 3 0 1 2 2 0 1 6 best Rgds Rajeev Reply 0 0 Deeba about 6 years ago #17995 This comment was minimized by the moderator on the site Hi 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 Expected Result: 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) Can anyone please help me resolve it. Reply 0 0 Viral Shah about 5 years ago #18225 This comment was minimized by the moderator on the site Hello , How I can split one cell in two row to convert coloum. Example Cell A1 :. Meagan Vakariya I want above data in two coloum Pls any one resolve it.. Reply 0 0 rafeeq about 5 years ago #18460 This comment was minimized by the moderator on the site 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 want the B1 period in split in two cloumn Reply 0 0 Gokul Anand about 5 years ago #18643 This comment was minimized by the moderator on the site 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 Reply 0 0 Sadhik about 5 years ago #18810 This comment was minimized by the moderator on the site 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 Reply 0 0 Rajiv Kumar about 5 years ago #19027 This comment was minimized by the moderator on the site 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. Reply 0 0 bla about 5 years ago #21084 This comment was minimized by the moderator on the site https://support.office.com/en-us/article/Transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744 How do I paste one value into multiple cells in Excel?Insert the same data into multiple cells using Ctrl+Enter. Select all the blank cells in a column.. Press F2 to edit the last selected cell and type some data: it can be text, a number, or a formula (e.g. "_unknown_"). Press Ctrl+Enter instead of Enter. All the selected cells will be filled with the data that you typed.. How do I copy and paste one value into multiple cells?Copy Using Standard Shortcut Keys in Excel. Select the cell which would like to copy.. Press Ctrl+ C keys to copy the Cell.. Select multiple cells, which is your target range of cells.. Now press Ctrl+ V keys to paste.. How do I split data from one cell to multiple rows?Split cells. Click in a cell, or select multiple cells that you want to split.. Under Table Tools, on the Layout tab, in the Merge group, click Split Cells.. Enter the number of columns or rows that you want to split the selected cells into.. How do I paste values into multiple selections?To keep the worksheet formulas, when copying and pasting multiple selections, follow these steps:. Copy the multiple selections.. Right-click the cell where you want to start the paste.. In the pop-up menu, click Paste Special.. In the Paste Special window, click OK.. |