Matt Jones
asked on
Copying data from a table in Word 2010 to Excel 2010
I have a table in Word 2010 that contains fairly verbose data in certain cells. Some cells have multi-line data containing bulleted lists, newlines / paragraphs, and all sorts of formatting. Let us refer to these cells as "Complex Cells"
My GOAL: copy this data from this Word 2010 table to Excel 2010, and have EACH cell from the table in Word end up as a cell in Excel.
However, when I copy the table and paste it into Excel, the data from a single Complex Cells ends up in MULTIPLE fields in Excel. For instance, a bulleted list from a table cell ends up spread across multiple rows in Excel.
How do I copy the Complex Cell data from Word to Excel and retain the original cell structure?
My GOAL: copy this data from this Word 2010 table to Excel 2010, and have EACH cell from the table in Word end up as a cell in Excel.
However, when I copy the table and paste it into Excel, the data from a single Complex Cells ends up in MULTIPLE fields in Excel. For instance, a bulleted list from a table cell ends up spread across multiple rows in Excel.
How do I copy the Complex Cell data from Word to Excel and retain the original cell structure?
It might help to post a small, non confidential sample.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi fanpages,
The F2 before paste does work; however it works for ONLY one cell of Complete Data at a time. If I have to copy and past one cell at a time, there are multiple ways of accomplishing the goal but it will take too long. Instead of F2, I can also double click before doing a paste. However, this too works for only ONE cell at a time.
The table has hundreds of cells that contain Complex Data. How do I copy MULTIPLE Complex Cells from Word table and paste into Excel in one shot?
The F2 before paste does work; however it works for ONLY one cell of Complete Data at a time. If I have to copy and past one cell at a time, there are multiple ways of accomplishing the goal but it will take too long. Instead of F2, I can also double click before doing a paste. However, this too works for only ONE cell at a time.
The table has hundreds of cells that contain Complex Data. How do I copy MULTIPLE Complex Cells from Word table and paste into Excel in one shot?
ASKER
Grahamskan,
Sample file attached... It has ONE row from the table; the actual table has about 25000 rows.
The data from a SINGLE Word table cell (shown below) is placed in MULTIPLE rows in Excel when I copy and paste. Please note that this is a HUGE table; I tried to copy and paste the ENTIRE table.
IN WORD 2010, IN A SINGLE TABLE CELL:
Benefits
• Updates 99% of free disk space without harming drive.
• It takes 2 passes before wipes.**
• From the #1 anti-virus vendor in Finland. *
**In case of virus detection, the process aborts.
SHOWS UP IN SIX ROWS WHEN COPIED AND PASTED INTO EXCEL 2010
sample.docx
Sample file attached... It has ONE row from the table; the actual table has about 25000 rows.
The data from a SINGLE Word table cell (shown below) is placed in MULTIPLE rows in Excel when I copy and paste. Please note that this is a HUGE table; I tried to copy and paste the ENTIRE table.
IN WORD 2010, IN A SINGLE TABLE CELL:
Benefits
• Updates 99% of free disk space without harming drive.
• It takes 2 passes before wipes.**
• From the #1 anti-virus vendor in Finland. *
**In case of virus detection, the process aborts.
SHOWS UP IN SIX ROWS WHEN COPIED AND PASTED INTO EXCEL 2010
sample.docx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To clarify, I'm thinking that we could replace the paragraph marks with spaces before copying. This would mean that line breaks would not necessarily appear as seen in the Word table cell, but at least all the text in the Word cell would appear in a single Excel cell
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, experts! hgholt, your solution works BEAUTIFULLY!
I have the same problem - but sadly do not understand the answer here i.e. how to use VBA? I also need to keep the colours of the text in the WORD table when transferred to EXCEL. I am using Ofiice 2010 - I am able to copy past one cell at a time and keep all formating but not the text colours. Is it possible? THank you in advance for a prompt reply
The question is solved (closed), so you should ask a new question, instead of continuing this old one from last year.
You could make a reference to this question.
When a cell with more lines in a Word table are copied to Excel, it is divided into more than one row.
The VBA code assemble the rows into the first cell, making a line feed for each, so the character colours for all will be the colour in the first cell, = the first line in the Word cell.
It should be possible to read the character colours, and assign them individually.
Probably there are things in your Word document, that are different from the question here, so upload a sample Word document.
- Ejgil
You could make a reference to this question.
When a cell with more lines in a Word table are copied to Excel, it is divided into more than one row.
The VBA code assemble the rows into the first cell, making a line feed for each, so the character colours for all will be the colour in the first cell, = the first line in the Word cell.
It should be possible to read the character colours, and assign them individually.
Probably there are things in your Word document, that are different from the question here, so upload a sample Word document.
- Ejgil