Alok Paul
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.
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 (3 Easy Methods)
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_xThis declares different variables.
Application.ScreenUpdating = FalseTurns off the Screen update feature.
application.displayalerts = falseTurns off the alerts and messages while a macro is running.
Set xWork_Book = Application.ActiveWorkbook
Set xWork_Sheet = xWork_Book.Worksheets.AddSet the workbook and worksheet correspondingly.
Set object_doc = Word_App.Documents.Open(Filename:=Word_Name, ReadOnly:=True)
object_doc.ActivateSets the object document.
Set RPP_x = object_doc.Range(Start:=object_doc.Paragraphs(1).Range.StartSets another object RPP_x.
On Error Resume NextIf an error is found enter the go to the next section.
Word_App.Selection.CopyCopy the selected portion of the Word.app.
If Len(Name_1) > 31 Then
   Name_1 = Left(Name_1, 31)
End IfAn If condition is applied.
Word_App.DisplayAlerts = True
Word_App.ScreenUpdating = TrueTurn on the display alert and screen updating.
Read More: How to Convert Word Table to Excel Spreadsheet (6 Methods)
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.
