title: “How To Export Data From A Word Form To Excel” ShowToc: true date: “2022-12-22” author: “Thomas Moreland”
Understand Word Forms
A Word form contains fill-in blanks that make it easy for people to fill in important information. Usually, these forms are distributed so that many people can fill them out and return them. Collecting all of the information from those forms and transferring them to an Excel spreadsheet is a tedious manual process. Thankfully, it’s very easy to export Word form data to Excel using comma-delimited files.
Use Options to Select Comma-Delimited Format
It only takes a few adjustments in your Word form document to save the form data as a comma-delimited (CSV) file. This file format separates each form record with commas. Use CSV files to import data into Excel.
Microsoft Word is smart enough to only save the data from the form into the CSV file. The extra text in the document is not added to the CSV file.
With the form document open and filled in with data, select File > Options.
Select Advanced in the panel to the left of the Options dialog box. In the Preserve fidelity when sharing this document section, place a check in the box next to Save form data as delimited text file. Press OK to close the Word Options dialog box.
At this point, there are two ways to export the data to Excel. Either save the document or export the data.
Use Save As to Export to CSV
To save the document to CSV format:
From the File menu, select Save As. The Save As dialog box opens with the TXT file format already selected. Name the file, and select OK. A dialog box appears with a preview of the data you’re exporting from the form into CSV format. Press OK to export the data.
Word extracts the form data that was typed into the form as a .csv file, which can be opened with Excel. Word inserts commas between the fields. Excel uses the commas to separate the data into specific cells.
Use Export to Save to CSV
To export the data to a CSV file:
Select File > Export. Select Change File Type. Under Document File Types, select Plain Text (*. txt). Select Save As to open the Save As dialog box. Choose the location where you want to save the file, enter a filename, and select Save to export the data.
Import Form Data Into Excel
After the Word form data is exported to CSV format, the data is ready to import into Microsoft Excel.
If you open the file using Excel, you won’t see the file in the folder where you saved it because Excel defaults to showing you only Excel files.
Open the CSV file following these steps:
Open a blank Excel document. Select File > Open. Select Browse. In the file type drop-down list, choose All Files (*. *). Browse to the folder where the exported Word form data is located, and select the CSV text file. Select Open. Excel opens the Text Import Wizard. Choose Delimited, and select Next. Deselect the Tab checkbox, and select the Comma check box. Then, select Next. Select Finish.
The data from your Word form appears in your Excel sheet.
Import Form Data Into Existing Spreadsheet With Excel 2019 and 2016
If you want to bring data from new Microsoft forms into the same spreadsheet, follow these steps:
Open your saved Excel file. Create a new sheet by pressing the + symbol at the bottom of the existing sheet. Select cell A1. In the Data menu, select Get Data > From File, > From Text/CSV (In Microsoft 2010 or 2013, select From Text). Select the new text file with the form data, and select Import. In the Wizard, select Load > Load To. Under Where do you want to put the data, select Existing worksheet, and make sure A1 is selected. Press OK.
Once the form data is imported, add the new Word form data to your primary worksheet:
In Word, highlight the entire row with just the data. Right-click, and select Copy. In Excel, select your main worksheet. Highlight the cell just below the last record in the sheet. Right-click, and select Paste.
Import Form Data Into Existing Spreadsheet With Excel 2013 and 2010
To bring data from new Microsoft forms into Excel 2013 or 2010, follow these steps:
Open your saved Excel file. Select the cell in the first column just below your existing data. Select Data > From Text. Select the new text file with the form data, and select Import. In the Wizard, choose Delimited, and press Next. Deselect the Tab checkbox, select the Comma check box, and select Next. Press Finish. Under Where do you want to put the data, select Existing worksheet. Make sure the cell where you want the new row to go is selected. Press OK.
This adds the new Word form data to the very next row in your primary worksheet.
