Software

Transfer data from Word forms to an Excel worksheet

Avoid the hassle of manually importing Word form data into Excel. With the help of an Excel wizard, you can quickly step through the process.
Word forms provide a convenient way to gather information from users or clients so that you can analyze it later. But bringing the data into Excel can be a big chore. To avoid having to manually enter the data into a worksheet, let Excel's Text Import wizard bring it in for you. To demonstrate, let's say you've asked employees to complete the form shown in Figure A.

Figure A

Follow these steps to save the data in each completed form to a text file that can be imported into Excel:

  1. Open one of the completed forms.
  2. Go to Tools | Options, click the Save tab, and select the Save Data Only For Forms check box. (In Word 2007, click the Office button, click Advanced, scroll to Preserve Fidelity When Sharing This Document, and select the Save Data As Delimited Text File check box.)
  3. Click OK.
  4. Save the file as a .txt file.
  5. When the File Conversion dialog box appears (Figure B), click OK.

Figure B

You can now import the data in the text files into a spreadsheet by following these steps:

  1. Open a blank worksheet in Excel.
  2. Go to Data | Import External Data | Import Data. (In Excel 2007, click the Data tab, click Get External Data, and then select From Text.)
  3. Click the text file you want to import, then click Import.
  4. Select the Delimited option (Figure C) and then click Next.

Figure C

  1. For this example, clear the Tab check box and then select the Comma check box (Figure D).

Figure D

  1. Click Next and then click Finish.
  2. Click in cell A1 and then click OK.

Figure E shows the resulting data. Note that headers have been inserted above the data in row one.

Figure E

To import the second text file, you just open the same Excel worksheet and click in the second row below the last row of data; in this case, A4. (The wizard forces you to skip a row each time you add a new row of data. You can delete these blank rows later.)

Important note: If you need to enter more than a handful of forms a day, you should consider using ADO (ActiveX Data Object) to create a code connection to the worksheet that will automate the data transfer process.


Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

14 comments
pgg1507
pgg1507

I had the same issue as DBHalbur, until I realised this is to copy/import data into excel from a word form. NOT copying the form itsel.

To copy the MS Word form into Excel, simply Copy/Paste Special/HTML after I did this I had to adjust some cell merge, but after a few minutes I had the result I wanted.

ThirdSide
ThirdSide

For those looking to find out more on automation using ADO, a starting point would be http://support.microsoft.com/kb/295646 but for the non-technical folk, you may want to consider convertng your Word form to a PDF form using adobe acrobat. Acrobat allows you to pull multiple forms at once into a csv file that will open in Excel with the headers and values.

DBHalbur
DBHalbur

When I get to Fig B. - I don't see any text in the preview section. Also, when I try to save the file as a .txt file I only have the option to "Save a Copy As..." Needless to say when I attempt to import the text into Excel - I get zip. Any ideas?

ehoulroyd
ehoulroyd

Important note: If you need to enter more than a handful of forms a day, you should consider using ADO (ActiveX Data Object) to create a code connection to the worksheet that will automate the data transfer process.

Stelutsa
Stelutsa

Thank you for the useful information! But is it possible to transfer data automatically from many word files into a single excel file, without opening each word document? I have to do it with 130 word completed forms and I am not very excited of the idea to repeat the operation you described above 130 times...

ericnumbers
ericnumbers

have searched for an article on how-to "using ADO (ActiveX Data Object) to create a code connection" metioned at the end of this article but have not been able to find anything. Can you help me an article that covers this subject?

packpeep24
packpeep24

This functionality is exactly what I'm looking for. However, in Word 2007, when I attempt to save as .txt, the screen in Fig B comes up & I select OK but the file does not save.

shairali123
shairali123

Ms Excel worksheet importantnd short cuts required

pgg1507
pgg1507

@DBHalbur I had the same issue, until I realised this is to copy/import data into excel from a word form. NOT copying the form itsel.

To copy the MS Word form into Excel, simply Copy/Paste Special/HTML after I did this I had to adjust some cell merge, but after a few minutes I had the result I wanted.

mbandsb
mbandsb

I have had the same problem with Word 2007. Followed all the steps but nothing appears in the preview. I have hashed out a workaround which may or may not be a solution for you. It would be tedious if you have a large number of forms to process and the instructions are specifically for Word 2007. In my template, I set the style of each field to be exactly the same (for each field click on properties and select style) and ensure that the style selected is not used anywhere on the form other than in the data fields. Make sure you save this document as a template from which response documents are generated. To save data from completed response documents: 1. unprotect the document (if it is protected) 2. click the cursor on (select) the data in the first field. 3. Select the 'Home' tab and click on "select text with similar formatting" - this should highlight all the data in the document and nothing else. 4. Press control C (copy) 5. Open a new document in Notepad 6. Paste the text. Hopefully, the response from each field will appear on a new line. 7. save the notepad file. You should be able to open your excel spreadsheet and import the data by selecting Data>Get external data from text and then select the notepad file you have just created. I hope this is of some help.

texaskid
texaskid

Please let me know what you were able to find. I have this exact project. Thanks

pgg1507
pgg1507

@tiffanynoelle I had the same issue, until I realised this is to copy/import data into excel from a word form. NOT copying the form itsel.

To copy the MS Word form into Excel, simply Copy/Paste Special/HTML after I did this I had to adjust some cell merge, but after a few minutes I had the result I wanted.

Editor's Picks