There are at least two different issues. The code macropod has provided
should deal with one of them.
However, the other one is that if you do have mixed data types (e.g.
text and numeric in an Excel column, the method that Word uses by
default to get the data (an OLE DB provider) has to decide which type to
use. If it decides "numeric", the texts will be converted to 0. Once you
have entered a ZIP as a text, even changing the cell format to General
or numeric does not actually change the format of the existing content.
(typically you can see which are texts and which are numeric by making
the column wider - with standard formatting the texts will be
left-aligned and the numeric ones will be right-aligned, but with
non-standard formatting you may not be able to tell)
As far as I can tell, if you have a mixture of 5- and 9-digit ZIPs,
there is no single format in Excel that can format both of them
properly. But AFAICS keeping to a numeric format is probably the best
thing to do as far as Excel is concerned. So, if you can see that they
are almost all numeric except for a few 'text" ones, ensure that the
cells are formatted as numeric and re-enter the "text" ones.
Another short-term solution may be to switch to using DDE as the
connection method - check Word Tools-Options-General-Confirm
conversions at open, go through the connection process again, and choose
the DDE option when offered.
Peter Jamieson
http://tips.pjmsn.me.uk
On 16/12/2009 20:23, N wrote:
I am creating a merge for address labels in Word 2003, pulling from an Excel
2003 file. All rows in Excel are formatted the same with the zip code as
general and includes either 5 or 9 numbered codes. When I select my file
from Word and it opens the Mail Merge Recipients dialog box some fields show
a 0 instead of the zip code data. I've double checked the formatting and
changed it to number from general but it continues to occur. If I continue
with the merge those 0 fields transfer to the labels (City, ST, 0) and I am
left with random multiple addresses that are incomplete.
The list is 478 rows so going row by row to fix it for each dropped field
per label is very tedious and time consuming.
Any suggestions as to what I'm doing wrong? Thanks in advance.