How to Convert Word to Excel with Columns (2 Methods)

Sometimes we want to extract the information from the MS Word file into MS Excel. As we know, Excel is based on rows and columns. So, the data of the word file will be stored in rows and columns. In this article, we will two quick methods on how to convert a Word file to Excel with columns.

Convert Word to Excel with Columns: 2 Easy Methods

Here, we will discuss two methods. One is a manual step-by-step process, the other is a VBA-based process. In our discussion, we will consider the following Word file as our dataset.

1. Convert Word to Text and Then to Excel Combining ‘Save a Copy’ and ‘From Text/CSV’ Commands

In this method, we will save the Word file in Text or CSV format first, using the Save a Copy command (or, the ‘Save As’ command in version).  Then we will import the data from the converted Text or CSV file to Excel. Follow all the steps below.

Steps:

• First of all, press the File Then, click on the Save a Copy button.

• Now, save the file in the desired location of the File Explorer. Choose the File type as Plain Text.
• Finally, click on the Save button.

In this step, we convert the word file into a text file.

• The File Conversion window will appear.
• Select the Windows(Default) option and then press OK.

• Now, open your Excel file. Choose the Data tab.
• Go to the Get Data group.
• After that, select From Text/CSV of the From File drop-down.

• Choose the converted text file from the File Explorer. Then, press the Import button.

• Then, the File Origin window appears.
• Choose Custom as Delimiter.
• Finally, press the Load option.

• Look, at the Excel sheet.

So here it is! Our Word file is successfully converted to Excel with data and columns.

Read More: How to Import Data from Word to Excel

2. Use a VBA Code to Convert Word to Excel

If you are a VBA freak like me, here is a VBA code for you in this regard. In this section, we will use a VBA code to convert Word into Excel. Here are the steps below to do that.

Steps:

• First of all, we store the data in the word in a table.

• Now, go to the bottom part of the Excel file where you can see the sheet names.
• Go to your desired sheet where you want to import data from the Word file.
• Click on the sheet name.
• Press the right button of the mouse.
• Choose the View Code option from the Context menu.

• VBA window appears now.
• Choose the Module option from the Insert tab.

• The Module appears now. We will write the VBA code here.

• Now, copy and put the following VBA code on the VBA module and then Save it.
• Then, press the F5 button to run the code.

Sub convert_Word_to_Excel()
Dim object_doc, Word_App As Object
Dim Word_Name As Variant
Dim xWork_Book As Workbook
Dim xWork_Sheet As Worksheet
Dim Name_1 As String
Dim PC_x, RPP_x
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Word_Name = Application.GetOpenFilename("Word file(*.doc;*.docx) ,*.doc;*.docx", , "Select now")
If Word_Name = False Then Exit Sub
Application.ScreenUpdating = False
Set xWork_Book = Application.ActiveWorkbook
Set xWork_Sheet = xWork_Book.Worksheets.Add
Set Word_App = CreateObject("Word.Application")
Word_App.ScreenUpdating = False
Word_App.DisplayAlerts = False
Set object_doc = Word_App.Documents.Open(Filename:=Word_Name, ReadOnly:=True)
object_doc.Activate
PC_x = object_doc.Paragraphs.Count
Set RPP_x = object_doc.Range(Start:=object_doc.Paragraphs(1).Range.Start, End:=object_doc.Paragraphs(PC_x).Range.End)
RPP_x.Select
On Error Resume Next
Word_App.Selection.Copy
Name_1 = object_doc.Name
Name_1 = Replace(Name_1, ":", "_")
Name_1 = Replace(Name_1, "\", "_")
Name_1 = Replace(Name_1, "/", "_")
Name_1 = Replace(Name_1, "?", "_")
Name_1 = Replace(Name_1, "*", "_")
Name_1 = Replace(Name_1, "[", "_")
Name_1 = Replace(Name_1, "]", "_")
If Len(Name_1) > 31 Then
    Name_1 = Left(Name_1, 31)
End If
xWork_Sheet.Name = Name_1
xWork_Sheet.Range("A1").Select
xWork_Sheet.Paste
object_doc.Close
Set object_doc = Nothing
Word_App.DisplayAlerts = True
Word_App.ScreenUpdating = True
Word_App.Quit (wdDoNotSaveChanges)
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

• Now, choose the Word file from the File Explorer, then click the Open button.

• Now, look at the Excel sheet.

Code Explanation:

Dim object_doc, Word_App As Object
Dim Word_Name As Variant
Dim xWork_Book As Workbook
Dim xWork_Sheet As Worksheet
Dim Name_1 As String
Dim PC_x, RPP_x

This declares different variables.

Application.ScreenUpdating = False

Turns off the Screen update feature.

application.displayalerts = false

Turns off the alerts and messages while a macro is running.

Set xWork_Book = Application.ActiveWorkbook
Set xWork_Sheet = xWork_Book.Worksheets.Add

Set the workbook and worksheet correspondingly.

Set object_doc = Word_App.Documents.Open(Filename:=Word_Name, ReadOnly:=True)
object_doc.Activate

Sets the object document.

Set RPP_x = object_doc.Range(Start:=object_doc.Paragraphs(1).Range.Start

Sets another object RPP_x.

On Error Resume Next

If an error is found enter the go to the next section.

Word_App.Selection.Copy

Copy the selected portion of the Word.app.

If Len(Name_1) > 31 Then
    Name_1 = Left(Name_1, 31)
End If

An If condition is applied.

Word_App.DisplayAlerts = True
Word_App.ScreenUpdating = True

Turn on the display alert and screen updating.

Read More: How to Convert Word Table to Excel Spreadsheet

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, we described how to convert a Word file into Excel with columns. I hope this will satisfy your needs. Please give your suggestions in the comment box.

Related Articles

• How to Convert Word to Excel but Keep Formatting
• Copy from Word to Excel into Multiple Cells
• How to Link Word Document to Excel
• How to Convert Word Picture to Excel Table

<< Go Back to Import Word to Excel | Importing Data in Excel | Learn Excel