Converting names/addresses in Word to an Excel File

[Trevor Mitchell]

I used to use Wordperfect (and don't have this software anymore) but no longer use it and saved a Wordperfect Data (.DAT) file to a Word Document which contains names and addresses.
It has created one name/address per Word Page as attached.



I want to do a MailMerge to print address labels. How can I convert this Word Document to an Excel one with each name/address on a separate row ?

[macropod]

Cross-posted at: Converting a Word File with addresses to Excel
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

[macropod]

What separates the lines of each page - paragraph breaks or line breaks? Also, what creates the page breaks - Section breaks or page breaks? It's impossible to tell either of these things from your screenshot.

[matt_henry]

1
Open the address list in Word and arrange the entries so that each address is separated by a delimiting character. You can choose any character to delimit the fields, although it is best to use a character that does not otherwise occur in your data. For example, if you were using semicolons to delimit the addresses, your file might read “1 Example Street, New York, NY;2 Example Street, New York, NY;” and so on.

2
Click “File” and select “Save As” to open the Save dialog box. Browse to the location you want to save the file to, and enter a name into the “File Name” box. Select “Text File” from the Save As Type drop-down menu, and then click “Save” to save the file as plain text.

3
Close Word and open Excel. Click “File,” then “New” and then double click on “Blank Workbook” to open a new Excel spreadsheet.
Select the “Data” tab and then click on the “From Text” button. The Import Text File dialog box displays. Browse to the location of your saved text file, select it and then click the “Import” button to open the Text Import Wizard.

5
Click the “Delimited” radio button in the Original File Type field, and set the Start Import At Row option to “1.” Click “Next” to open the next page of the wizard.

6
Select the delimiter character you used in your text file by clicking on the corresponding check box. By default, Excel provides as choices Tab, Semicolon, Comma and Space as delimiters. If you used a different character, check “Other” and enter the character into the text field. Click “Next” to go to the final page of the wizard.

7
Select “General” from the Column Data Format list and then click “Finish” to open the Import Data pop-up box. Select the “Existing Worksheet” radio button and click “OK” to finish importing the list.

I hope these steps will be helpful!
Matt Henry

[macropod]

Since the OP elected not to respond to post #3, which was almost 6 months ago, it is reasonable to assume it's no longer a pressing issue.

Also, it's all very well to say "arrange the entries so that each address is separated by a delimiting character", but simply replacing line breaks with tabs or semi-colons won't ensure the data are properly restructured for a mailmerge. The single record depicted by the OP shows an addressee with two 'name' lines and four 'address' lines, plus and empty line between the last two 'address' lines. If the next record has one 'name' line and four 'address' lines, your approach will result in name & address data appearing in the same column and none of 'address' lines aligning. It gets even worse if there are five 'address' lines in some records.

Finally, if tabs are used for the record separators, these is no need for your convoluted steps 2 - 7; the data can simply be copied and pasted into Excel from Word.