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