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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

It might help to post a small, non confidential sample.
[ fanpages ]IT Services ConsultantCommented:

If you select & copy a single cell of "complex data" from the Word table & then "click into" a single cell of the destination MS-Excel worksheet, press [F2] & then paste ([CTRL]+[V]) can you see the desired result?

(Pressing the [F2] key before pasting is the key step here)


jmohsinAuthor Commented:
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?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jmohsinAuthor Commented:
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.

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

Yes. The presentational differences are a reflection of the entirely different objectives of Excel (a calculation-based application) and Word which is intended for text publication.

Hard-coded new lines,  whether new (Word) paragraphs or not, are pasted as separate cells and hence separate rows in the spreadsheet.

It might be possible to create a different presentation using VBA coding, but the desired Word formatting might be difficult to represent. Do you have a specific objective, other than getting the whole paragraph in one cell, that differs from that of default copy/paste process?
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
[ fanpages ]IT Services ConsultantCommented:
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.

Yes, I appreciate that I asked you to copy a single cell of data.  The point for asking was to establish if copy'n'paste was working as expected.  It appears it is.

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?

After establishing that the copy'n'pasting function is performing as intended (by design), I was going to suggest something similar to what GrahamSkan mentioned:

a) Write a Visual Basic for Applications routine to retrieve the data from the MS-Word document & paste (as you intended) into an MS-Excel worksheet, or

b) Find/Replace all line & paragraph breaks to a unique combination of characters not found anywhere else within your MS-Word document text, for example, line-breaks to "[CR]" & paragraph breaks to "[CRLF]" (or similar), copy the resultant text to the Windows Clipboard, paste into MS-Excel, then Find/Replace the unique character string(s) back to their ASCII code equivalents (so that the original formatting is returned to the text once it is within the MS-Excel worksheet cells).
Rob HensonFinance AnalystCommented:
I believe more recent versions of Office are better than their predecessors at integrating information across applications. Have you tried embedding the Word document into your Excel sheet? This will give you the full versatility of Word formatting in the Excel environment.

Conversely, if you require mathematical data in your scenario would embedding an Excel workbook into the Word document be an option. Again this gives the full versatility of an Excel sheet within a Word document. The downside to this way round would be the size limitations of fitting your Excel sheet into the Word page. Excel allows you to scale/zoom the print Area to fit onto a specified page size.

If this is not a suitable suggestion, what is the purpose of showing the Word information in an Excel file? Are you needing to do any further calculations on the Word information.

Rob H
Ejgil HedegaardCommented:
Her is a VBA solution using copy from cells in the Word tables, paste in Excel with all the lines in different cells, and then combining the lines into one cell, deleting the rest.

Activate a reference to Microsoft Word Object Library in VBA menu Tools-References.
And set the path and file name to the word file in the line
Set doc = Word.Documents.Open("Full path to Word file")

Option Explicit

Sub WordTableImport()
    Dim obWord As Object
    Dim doc As Word.Document
    Dim docTable As Word.Table
    Dim tblRow As Long, tblCol As Long
    Dim ws As Worksheet
    Dim xlsRow As Long, xlsRowMax As Long, xlsRow1 As Long
    Dim strDocContent As String, intPos As Integer
    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    Set obWord = CreateObject("Word.Application")
    Set doc = Word.Documents.Open("Full path to Word file")
xlsRow = 0
    For Each docTable In doc.Tables
        For tblRow = 1 To docTable.Rows.Count
            xlsRow = xlsRow + 1
            For tblCol = 1 To docTable.Columns.Count
                docTable.Cell(tblRow, tblCol).Range.Copy
                ws.Range(Cells(xlsRow, tblCol), Cells(xlsRow, tblCol)).Select
                xlsRowMax = ws.UsedRange.Rows.Count
                If xlsRowMax > xlsRow Then
                    Do While Len(ws.Cells(xlsRowMax, tblCol)) = 0 And xlsRowMax > xlsRow
                        xlsRowMax = xlsRowMax - 1
                    If xlsRowMax > xlsRow Then
                        strDocContent = ws.Cells(xlsRow, tblCol)
                        For xlsRow1 = xlsRow + 1 To xlsRowMax
                            strDocContent = strDocContent + Chr(10) + ws.Cells(xlsRow1, tblCol)
                        Next xlsRow1
                        For intPos = 1 To Len(strDocContent)
                            If Asc(Mid(strDocContent, intPos, 1)) = 183 Then
                                Mid(strDocContent, intPos, 1) = Chr(149)
                            End If
                        Next intPos
                        ws.Cells(xlsRow, tblCol) = strDocContent
                        ws.Range(Cells(xlsRow + 1, tblCol), Cells(xlsRowMax, tblCol)).Clear
                    End If
                End If
            Next tblCol
        Next tblRow
    Next docTable

    Set obWord = Nothing
    With ws.Range(Cells(1, 1), Cells(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count))
        .ColumnWidth = 50
        .VerticalAlignment = xlTop
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Color = -16777216
            .Weight = xlMedium
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Color = -16777216
            .Weight = xlMedium
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Color = -16777216
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Color = -16777216
            .Weight = xlMedium
        End With
        If ws.UsedRange.Columns.Count > 1 Then
            With .Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .Color = -16777216
                .Weight = xlMedium
            End With
        End If
        If ws.UsedRange.Rows.Count > 1 Then
            With .Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .Color = -16777216
                .Weight = xlMedium
            End With
        End If

    End With
    Application.ScreenUpdating = True
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmohsinAuthor Commented:
Thanks, experts! hgholt, your solution works BEAUTIFULLY!
Alexandra ManoliCommented:
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
Ejgil HedegaardCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.