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.


Download Practice Workbook

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


2 Methods to Convert Word to Excel with Columns

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.

We have used tab characters to create a table-like dataset in word which is worth handling with the first method. For the second method, you have to use a real Word table to deal with VBA. Like this. 👇


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.

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

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.

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

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

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

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

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

  • Look, at the Excel sheet.

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


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.

Use a VBA Code to Convert Word to Excel

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

Use a VBA Code to Convert Word to Excel

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

Use a VBA Code to Convert Word to Excel

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


Conclusion

In this article, we described how to convert a Word file into Excel with columns. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo