How to Convert Word to Excel but Keep Formatting (2 Easy Methods)

This article illustrates how you can convert word documents to excel spreadsheets but keep the same formatting. Suppose you need to format or sort your data that is in a word document. Obviously, excel is the better choice to do that. Fortunately, you don’t have to type the data again in the new excel file. You can just convert the word document to an excel spreadsheet while keeping the formatting. The following picture highlights the purpose of this article. Have a quick look through it to learn how to do that easily.

Convert Word to Excel and Keep Formatting


Download Practice Workbook

You can download the practice workbook from the download button below.


2 Ways to Convert Word to Excel with the Same Formatting

Imagine you have the following word document. Now you want to convert it to an excel spreadsheet. Then follow the methods below.

1. Convert Word to Excel with Copy-Paste

You can simply copy the data from the word document and paste it onto the excel sheet. Follow the steps below to do that.

📌 Steps

  • First, go to the word file. Then, press CTRL+A to select the entire document. You can also select a particular range only if required.
  • After that, press CTRL+C to copy the data as shown below.

  • Next, go to the excel spreadsheet. Then, select the upper-left cell of the range where you want to get the data.
  • Now, press CTRL+V to paste the data. Alternatively, you can right-click on that cell. Then, select Keep Source Formatting (K) from the Paste Options.
  • After that, you will get the following result.


2. Convert Word to Excel with VBA

You can do the same with Excel VBA. Follow the steps below to do that.

📌 Steps

  • First, add a new worksheet. Then, save the workbook as a macro-enabled workbook.
  • Next, press ALT+F11 to open the VBA window.
  • Then, select Insert >> Module to create a new module as shown in the picture below.

  • After that, copy the following code using the copy button.
Sub WordToExcelWithFormatting()
Dim Document, Word As Object
Dim File As Variant
Dim PG, Range
Application.ScreenUpdating = False
File = Application.GetOpenFilename _ 
("Word file(*.doc;*.docx) ,*.doc;*.docx", , "ExcelDemy.Com - Please Select")
If File = False Then Exit Sub
Set Word = CreateObject("Word.Application")
Set Document = Word.Documents.Open(Filename:=File, ReadOnly:=True)
Document.Activate
PG = Document.Paragraphs.Count
Set Range = Document.Range(Start:=Document.Paragraphs(1).Range.Start, _ 
End:=Document.Paragraphs(PG).Range.End)
Range.Select
On Error Resume Next
Word.Selection.Copy
ActiveSheet.Range("B2").Select
ActiveSheet.Paste
Document.Close
Word.Quit (wdDoNotSaveChanges)
Application.ScreenUpdating = True
End Sub
  • Then, paste the copied code onto the module window as shown below.

Convert Word to Excel with VBA

  • Now, press F5 to run the code. You can also do that from the Run
  • After that, you will be asked to select the word file that you want to convert.
  • Now, browse to the location of your desired word document. Then, select the file and click Open.

  • Finally, you will get a similar result as in the earlier method.

🔎 How Does the Code Work?

Sub WordToExcelWithFormatting()
We will write the code inside this subject procedure.

Dim Document, Word As Object
Dim File As Variant
Dim PG, Range
Declaring necessary variables.

Application.ScreenUpdating = False
This makes VBA work in the background. As a result, it works faster.

File = Application.GetOpenFilename _
(“Word file(*.doc;*.docx) ,*.doc;*.docx”, , “ExcelDemy.Com – Please Select”)
This allows the user to input the word document s/he wants to convert.

If File = False Then Exit Sub
The subject procedure ends if no input is given.

Set Word = CreateObject(“Word.Application”)
This sets the Word variable as a word document.

Set Document = Word.Documents.Open(Filename:=File, ReadOnly:=True)
This assigns the Document variable to the object or file referred to by the user.

Document.Activate
The referred document becomes activated.

PG = Document.Paragraphs.Count
This code line assigns the PG variable to the number of paragraphs in the word document

Set Range = Document.Range(Start:=Document.Paragraphs(1).Range.Start, _ End:=Document.Paragraphs(PG).Range.End)
This code is for detecting the entire range of the word document

Range.Select
Selects the range of data in the word document.

On Error Resume Next
Ignores if any error occurs and executes the next code line.

Word.Selection.Copy
Copies the selection in the word file.

ActiveSheet.Range(“B2”).Select
Selects the cell where the data will be imported. You can change this as required.

ActiveSheet.Paste
Pasting data copied from the word document onto the excel worksheet.

Document.Close
Word.Quit (wdDoNotSaveChanges)
Closes the word file without saving any changes.

Application.ScreenUpdating = True
Resets screen updating to its default value.

End Sub
Exits the subject procedure.


Things to Remember

  • You can also save the word file as a PDF. Then, use your PDF editor to convert it to an Excel Spreadsheet.
  • Don’t forget to save the workbook as a .xlsm Otherwise, you will lose the code.

Conclusion

Now, you know how to convert a word document to an excel spreadsheet and to keep the formatting as well. Please let us know if this article has helped you to solve your problem. You can also use the comment section below for further queries or suggestions. Do visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo