How to Export Data from Word to Excel
Microsoft Word provides users with the function to create simple forms and collect data in a standard format. Tables in Word documents can also organize data. Word can export this data into a file format that may be accessed by Microsoft Excel or database programs such as MS Access. ".TXT" and ".RTF" designate universal file formats for Text and Rich Text Format, respectively. Saving a copy of data in either format ensures that data can be imported into an Excel spreadsheet. Export and import wizards in Word and in Excel provide users with options and previews on how the data is formatted.
Launch Microsoft Word and open the desired data document by selecting "Open" under "File" in the top menu bar (or under the Office 2007 ribbon), selecting the file and clicking the "Open" button.
Check that each unit of data in the Word document is separated by a tab or a comma and that each record is separated by a line break.
Convert any tables to text by selecting "Select" under "Table" in the top menu bar and choosing "Table." Select the function under "Tools" to "Convert Table to Text" and choose the option to separate data by either a "Tab" or a "Comma" before clicking the "OK" button in the dialog box.
Select "Save As" under "File" in the top menu bar and type the name of the document.
Select "Rich Text Format (.rtf)" or "Text File (.txt)" from the drop-down menu next to "Format" and click the "Save" button.
Data from Word Form
Launch Microsoft Word and open the form by selecting "Open" under "File" in the top menu bar, choosing the desired file and clicking the "Open" button.
Select "Options" under "Tools" in the top menu bar and click the "Save" tab.
Check the option, "Save data only for forms" to check the box and click the "OK" button.
Select "Save a Copy" under "File" in the top menu bar.
Type the name of the document and select "Text Format (.txt)" as the file type before clicking the "Save" button. A File Conversion Wizard will open to accurately map the data.
Review the data displayed in the File Conversion Wizard and check that each unit of data is identified in quotes and separated with a comma. Confirm that blank data fields are registered as a blank space between two quotation marks before clicking the "OK" button.
Import Data into Excel
Launch Microsoft Excel and open a blank workbook.
Click the first cell in the spreadsheet and select "Import External Data" under "Data" in the top menu bar.
Select the option to "Import Data" and launch a Text Import Wizard.
Review each step in the Text Import Wizard to make sure that the fields are mapped correctly by selecting the "Delimited" options and previewing the distribution of data on the spreadsheet in the preview pane at the bottom of the dialog box. Click the "Next" button to navigate through the Text Import Wizard and click the "Finish" button to close the wizard and transfer the data.
Save the converted data in Excel format by selecting "Save" under "File" in the top menu bar, typing the file name, selecting "Excel Workbook" as the file format and clicking the "Save" button.
Tips & Warnings
- Single data sets such as from forms are imported into an Excel spreadsheet and produce a disabled second row. To continue importing data, ComputorCompanion.com recommends simply selecting the third row in the spreadsheet to insert data and deleting the second, unusable row.
- Microsoft Office 2007 (i.e., Word and Excel) users can access the functions through the top toolbar and Office ribbon in the applications.