Import Data (Sentence, Paragraphs, Tables, Comments) from Word to Excel

In our daily working life, we may need to import data from a Word file into an Excel file. In most cases, we would like to copy the text from Word file and then paste them into Excel file. But is there any convenient approach that we can complete the task especially when the text is very long or we need to split them into multiple records? Obviously, the answer is “Yes”. Text Import Wizard feature is one of the good options. Another one is Excel VBA programming.

Text Import Wizard

To import a Word document into Excel, you should save it as a text file before applying Text Import Wizard feature to convert text file into Excel file. With Text Import Wizard, there are two options available for you to break data into columns: Fixed-width option and Delimited option.

Fixed-width option

Suppose that I have a word file and the full name is Fixed Width.docx. It contains text shown in Figure 1.1.

Import data from word to Excel, Fixed width option

Figure 1.1

To save it into text file, open the file in Word, click the File tab -> Save As and then select on Plain Text in the Save as Type: list. By clicking on Save button, you will save the text file under the same path as that of .docx file. Suppose that the new text file has the same name with that of word file.

Now apply Excel to open Fixed Width.txt file. Please note that you need to change the file type drop-down list to Text Files.

Import data from word to excel figure 1.2

Figure 1.2

After you click on Open button in Figure 1.2, a Text Import Wizard dialog box shown in Figure 1.3 will be prompted. You can see that there are 3 steps in total.

Import data from word to excel figure 1.3

Figure 1.3

By clicking on Finish in step 3, you will get data like that in Figure 1.4. The text has been imported successfully. And there is only one column.

Import data from word to excel figure 1.4

Figure 1.4

If you look at Figure 1.3 closely, you will find that you can create or delete a break line at any desired position. You even can drag the break line to any position that you like. The right panel of Figure 1.5 shows what the data will be like if you create a break at position 10. The word “Not” was split up into two words “N” and “ot”.  In summary, you can create break lines to split text into several columns.

Import data from word to excel figure 1.5

Figure 1.5

Read More: How to Import Data from a Website to Excel?

Delimited option

Figure 2.1 shows you text from another word document (Delimited.doc).

Import data from word to excel figure 2.1

Figure 2.1

Use the same approach as above to convert word document into text file and open this text file in Excel. In the step 1, choose Delimited option. As for step 2, we need to select Other check box and enter “|” into the blank field after “Other:”.  This is because that the delimiters in our problem is “|”. You can see that the preview box has changed after you enter into “|”.

Import data from word to excel figure 2.2

Figure 2.2 [click on the image to get a full view]

Here is the final excel file. There are 6 columns in total.

Import data from word to excel figure 2.3

Figure 2.3

VBA programming

Most people don’t know that we can import data from word document into Excel file via VBA programming. In fact, a word document file is consists of multiple document objects. By manipulating these document objects, Excel VBA can retrieve almost all information from word document.

Open word document

First of all, I’d like to show you how to open and close a word document through VBA programming. Suppose that we put Fixed Width.docx under E:\. In order to let VBA know which document should be opened, we need to create object for Microsoft Word Application and word document respectively.  And finally make Microsoft Word Application to open word document.

Sub OpenDoc_Click()
 
 
 
'Microsoft Word object

Dim objWord As Object
 
Set objWord = CreateObject("Word.Application")
 
objWord.Visible = True
 
 
 
'Word document object

Dim WordNam As String
 
WordNam = "E:\Fixed Width.docx"
 
 
 
'Open word document

objWord.Documents.Open WordNam
 
 
 
End Sub

Close word document

Below code can be used to close above word document. You can also determine whether to save changes or not. Please note that object names should not be changed.

'objWord.Documents.Close

 
 
objWord.Quit SaveChanges:=wdDoNotSaveChanges

Word document objects

Here is a list of document objects in this web page. By referring to this page, you can know which objects can be manipulated. For example, “objWord.Documents(WordNam).Path” can retrieve document’s full pathname. “objWord.Documents(WordNam).Sentences.Count” can enable you know how many sentences in this word document. You can use Msgbox statement to prompt extracted information or put the extracted information directly into Excel file. “Thisworkbook.worksheets(2).cells(1,1) = objWord.Documents(WordNam).Sentences(1)” will enable you to extract the first sentenct into the cell A1 in the second worksheet. Following code give you a brief summary of document objects that you can manipulate via VBA.

Read More: How to pull/extract data from a website into Excel automatically?

You can run the code and see what will happen. I recommend you to replace “E:\Fixed Width.docx” and use your own word document. Because this document is simple and objects such as TOC, comment, table are not included.

Sub OpenDoc_Click()
 
 
 
Dim objWord As Object
 
Set objWord = CreateObject("Word.Application")
 
objWord.Visible = True
 
 
 
Dim WordNam As String
 
WordNam = "E:\Fixed Width.docx"
 
 
 
objWord.Documents.Open WordNam
 
 
 
MsgBox objWord.Documents(WordNam).Path
 
 
 
'MsgBox objWord.Documents(WordNam).Sentences.Count

'MsgBox objWord.Documents(WordNam).Sentences(3)

 
 
'MsgBox objWord.Documents(WordNam).Paragraphs.Count

 
 
'MsgBox objWord.Documents(WordNam).Tables.Count

 
 
'MsgBox objWord.Documents(WordNam).Words(40)

 
 
MsgBox objWord.Documents(WordNam).Bookmarks.Count
 
'MsgBox objWord.Documents(WordNam).Bookmarks(1)

 
 
'MsgBox objWord.Documents(WordNam).Characters.Count

'MsgBox objWord.Documents(WordNam).Characters(2)

 
 
'MsgBox objWord.Documents(WordNam).Comments.Count

'MsgBox objWord.Documents(WordNam).Comments(1)

 
 
'MsgBox objWord.Documents(WordNam).Content

 
 
'MsgBox objWord.Documents(WordNam).ActiveWindow.Panes(1).Pages(1).Breaks.Count

 
 
'MsgBox objWord.Documents(WordNam).Sections.Count

 
 
'MsgBox objWord.Documents(WordNam).ActiveWindow.Panes(1).Pages.Count

 
 
'MsgBox objWord.Documents(WordNam).Footnotes.Count

 
 
'objWord.Documents.Close

 
 
objWord.Quit SaveChanges:=wdDoNotSaveChanges
 
 
 
End Sub

Retrieve sentences

Let’s look back at the Fixed-width option part and see how to retrieve that poem into excel. It looks like that the poem is consists of a lot of sentences. We can retrieve one sentence after another using for loop based on the structure. “objWord.Documents(WordNam).Sentences.Count” can return the total number of sentences.

Sub Sentence_Click()
 
 
 
'Microsoft Word object

Dim objWord As Object
 
Set objWord = CreateObject("Word.Application")
 
objWord.Visible = True
 
 
 
'Word document object

Dim WordNam As String
 
WordNam = "E:\Fixed Width.docx"
 
 
 
'Open word document

objWord.Documents.Open WordNam
 
 
 
n = objWord.Documents(WordNam).Sentences.Count
 
For i = 1 To n
 
ThisWorkbook.Worksheets(2).Cells(i, 1) = objWord.Documents(WordNam).Sentences(i)
 
Next i
 
 
 
'Close objects

objWord.Documents.Close
 
objWord.Quit SaveChanges:=wdDoNotSaveChanges
 
 
 
End Sub

After running above code, you will get a worksheet similar to Figure 3.1. You can see that this is different from Figure 1.4. A special symbol exists between words.

Import data from word to excel figure 3.1

Figure 3.1

SPLIT function can be used to split sentences containing that special symbol and put each part into one row.

Import data from word to excel figure 3.2

Figure 3.2

After running the code, you will see a worksheet shown in Figure 3.3. Comparing against Figure 1.4, you can see that there are 3 blank rows. It was caused by the fact that the special symbol appears at the end of the second, third and fourth sentences.  Here just shows you how SPLIT function works and you can remove the blank rows manually or via code.

Import data from word to excel figure 3.3

Figure 3.3

Retrieve Paragraphs

Approach for retrieving paragraphs is similar to that for retrieving comments. Here is for your reference.

n = objWord.Documents(WordNam).Paragraphs.Count
 
For i = 1 To n
 
ThisWorkbook.Worksheets(2).Cells(i, 1) = objWord.Documents(WordNam).Paragraphs(i)
 
Next i

Retrieve tables

You can find an example about how to extract table data from word document into Excel file via VBA programming in this article.

Read More: How to Import Data into PowerPivot & Create Pivot Table/Pivot Chart

Retrieve comments

Sometimes, reviewer may add comments into your draft document and you many have to copy them from Word document into Excel file to track changes. If there are a lot of comments, you have to spend a lot of time on copy and paste. Is there any easier approach?

Suppose I have added two comments into Fixed Width.docx and make a new word document – Fixed Width Comments.docx. Following code can help you extract all comments.

Sub Comments_Click()
 
'Microsoft Word object

Dim objWord As Object
 
Set objWord = CreateObject("Word.Application")
 
objWord.Visible = True
 
 
 
'Word document object

Dim WordNam As String
 
WordNam = "E:\Fixed Width Comments.docx"
 
 
 
'Open word document

objWord.Documents.Open WordNam
 
 
 
ThisWorkbook.Worksheets(4).Activate
 
a = 1
 
For i = 1 To objWord.Documents(WordNam).Comments.Count
 
'Sequence number

ActiveSheet.Cells(a, 1) = a
 
ActiveSheet.Cells(a, 1).HorizontalAlignment = xlCenter
 
 
 
'Create date of comment

ActiveSheet.Cells(a, 2) = Format(objWord.Documents(WordNam).Comments(i).Date, "Short Date")
 
ActiveSheet.Cells(a, 2).HorizontalAlignment = xlLeft
 
 
 
'Comment

ActiveSheet.Cells(a, 3) = objWord.Documents(WordNam).Comments(i).Range.Text
 
ActiveSheet.Cells(a, 3).WrapText = True
 
a = a + 1
 
Next i
 
Columns("A").ColumnWidth = 5
 
Columns("B").ColumnWidth = 18
 
Columns("C").ColumnWidth = 70
 
 
 
'Close objects

objWord.Documents.Close
 
objWord.Quit SaveChanges:=wdDoNotSaveChanges
 
 
 
End Sub

Here is what returned by Excel after running above code.

Import data from word to excel figure 3.4

Figure 3.4

Retrieve TOC

Sometimes we need to compare TOC of two files and it will be time consuming if you compare TOC entries one by one. But using VBA, you can extract TOC entries into Excel file and then compare them one by one using for loop.  Here is where you can find code for extracting TOC.

Download working file

Download the working file from the link below.


I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as an SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet via programming. This will save me a lot of time. I am keen to learn new things.

1 Comment
  1. Reply
    Bojan March 7, 2017 at 8:58 PM

    Hello Zhiping,

    Great post. You helped me a lot.

    Can you please explain me how can I import the first line of the text (title) from every single Word page (I have about 250 pages) into Excel (separate sheets for every single title)?

    Kindest regards!

    Leave a reply