need to export data from word file to excel file

[jasoncarney]

I have msword (.doc) files that contain between 3 and 15 pages each. Each page in the file is laid out the same. i.e.:

name herman munster
address 1313 mockingbird lane
city anytown
state MT
zip 59829

Then the next page the same with different names/addr. What I am trying to do is extract the data drom the 30 different .doc files into a single spreadsheet containing all the data under the correct columns. (i.e. NAME, ADDRESS, CITY, STATE, ZIP)

Thanks in advance for any help...

Jason

 

[macropod]

Hi Jason,

If you open each file in Word and save it as an MSDOS text file, you'll have a plain text file with the data, looking like:

name herman munster
address 1313 mockingbird lane
city anytown
state MT
zip 59829

name herman munster
address 1313 mockingbird lane
city anytown
state MT
zip 59829

name herman munster
address 1313 mockingbird lane
city anytown
state MT
zip 59829

name herman munster
address 1313 mockingbird lane
city anytown
state MT
zip 59829

If you open the text file, copy & paste the data into Excel, you can then use Excel's 'Text to Columns' function to separate the 'name' 'address' etc descriptors from the data.

If you do this on Sheet1, the following formula in cell A1 of Sheet2 (or any other worksheet) will get the first person's name:
=OFFSET(Sheet1!$A$1,(ROW()-1)*6+COLUMN()-1,1)
Copy this across to E1 to get all the fields, then copy down as far as necessary to get all the records. You'll know you've run out of records when the formula returns only 0s.

Finaly, copy the results and use Edit|paste Special|Values to convert the formulae to the displayed results.

Cheers

[MS MVP - Word]

 

[jasoncarney]

Thank you for your help. I am having a problem getting the "Text to Columns" function to work. (I think)
I follow your instructiuons re: saving as a msdos .txt file then copy and paste into excel. Then I select column A, click 'DATA" then "text to columns" next and finish.
Then went to a1 of sheet 2 and input the formula:
=OFFSET(Sheet1!$A$1,(ROW()-1)*6+COLUMN()-1,1)

but recieved 0 as a value. I copied the formula to each cell from a1 to e8 but recieved 0 as a value in each of those as well...

not to confuse the issue, but I found a program to convert the files from .doc to .xls on the web. however the end result was column A with the desciptor (name, address, phone#) and column D having the actual values (i.e. Herman Munster, 1414 mockingbird lane, anytown, usa)
I tried entering the formula above in sheet 2 on the version created by the document converter but all I got for values there was 0's as well.

Thanks again
Jason

 

[macropod]

Hi Jason,

When you use Excel's 'Text to Columns' function, use the 'fixed width' parameter and move the column separator to the left of the client data - if Excel offers more than one column separator, delete all the others.

You should then end up with all the field descriptors in column A and the client data in column B.

Cheers

[MS MVP - Word]