Mail Merge with Word

[Guest]

Hi - I am working on merging some addresses from Excel to Word. If the zip code has nine numbers, it appears as a "0" on word. Yes, I have gone and formatted the cells with the special format formula that is in help but they still show up as a "0". I have put a single quote in front of the zip codes so that they won't be viewed as a formula. I have increased the width of the column but that didn't work either. Do you have any thoughts?

 

[David McRitchie]

Hi Lana,
Best to format the column as text and enter them as text.
Then you can also sort the zip codes and have a meaningful
order with those with 5 digits and those with 9 digits.

Best to enter such zip codes as text into Excel then you
don't have the problem in MS Word. You can format them
in Word as Deborah and others more familiar with Word
might suggest. My preference and solution can be seen in
http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5
using a macro to fix what you already have. Enter new codes
as text then you work with Canadian zip codes as well, eh.

Notes on using Mail Merge with Excel as your database in
http://www.mvps.org/dmcritchie/excel/mailmerg.htm

Reminder: Be sure to have your Excel spreadsheet as the first worksheet
tab in your workbook.

To alleviate formatting problems such as with currency (and zip codes) so
you use what you see in Excel:
In the Confirm Data Source dialog box,
click the MS Excel Worksheets via DDE (*.xls), and then click OK.

Hi - I am working on merging some addresses from Excel to Word. If the zip code has nine numbers, it appears as a "0" on word.

Yes, I have gone and formatted the cells with the special format formula that is in help but they still show up as a "0". I have
put a single quote in front of the zip codes so that they won't be viewed as a formula. I have increased the width of the column
but that didn't work either. Do you have any thoughts?

 

[Dave Peterson]

This is the type formatting that David was writing about:


Debra Dalgleish posted this for a different question, but I bet you could modify
it for zip codes:



In the Mail Merge, after you select your Excel file as a data source,
you should see a 'Confirm Data Source' dialog box.
(If you don't see the dialog box, change the setting in Word --
under Tools>Options, General -- add a check mark to
'Confirm Conversion at Open')

From that list, choose 'MS Excel Worksheets via DDE (*.xls)', and your
formatting will be retained.

If you have to connect through a different source, you can format the
fields in the Word document. For example, to specify a number of decimals:

1. In Word, in the Main Document, press Alt+F9 to view the field codes.
2. Find the field code for the number. It will look something like:
{ MERGEFIELD FieldName }
3. Add a switch, to format the number with two decimal places.
For example:
{ MERGEFIELD FieldName \# "#,##0.00" }
4. Press Alt+F9 to hide the field codes.
5. Save the Main Document