Leading zero from Zip Code not print on Label Merge

[Steve]

I have an Excel 2003 spreadsheet with address ready for a mail merge. The
format of the ZIP field is "ZIP CODE". I then merge this file into a Word
2003 label document.

All zip codes with leading zeros (e.g. 03110) print without the leading
zero. Yet a five digit code such as 90123 prints fine.

What could be the cause of this?

Thanks,

Steve

 

[Suzanne S. Barnhill]

This is a flaw in the OLE DB connection used by Word as the default for
Excel data sources. You have two options:

1. You can connect via DDE instead, which preserves field formatting (but
does not pass any upper Unicode characters).

2. You can add a "numeric picture" switch to the merge field.

In order to be able to select DDE, you need to enable "Confirm conversion
at Open" in Tools | Options | General. For information on field formatting,
see http://www.gmayor.com/formatting_word_fields.htm

 

[Steve]

Can I force the Zip field in Excel to always be five digits? If so, I do I do
this?

I was hoping your response would be to format the zip field different than
ZIP Code.

Thanks

 

[Suzanne S. Barnhill]

I don't understand the question. I was assuming that the ZIP codes in your
Excel data source were already correct.

 

[Steve]

Yes, the zip codes in the spreadsheet are correct. For examples 03110 is
fine. However, when it shows up in the Word label report is shows as 3110.
As you can see, the Word label report has lost the leading zero.

 

[Suzanne S. Barnhill]

In that case you use one of the two solutions offered in my previous reply.

 

[Graham Mayor]

As Suzanne has already indicated, the default OLE DB interconnnection method
accesses pnly the raw data from Excel. It completely ignores your formatting
and thus leading zeros are stripped. You can revert to the older DDE
connection method, but it is a rather flaky method of connection and does
not always work reliably, so the simple solution is to use a formatting
switch. This is covered under the zip code section of the link Suzanne has
already posted http://www.gmayor.com/formatting_word_fields.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>