[n]

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.

[macropod[_2_]]

Hi N,

Try the merge with the following field code. It correctly formats 5-digit and 5+4-digit Zip Code data, even where the source data
may have incorrect formatting such as a hyphen followed by trailing 0s after plain 5-digit Zip Codes or is formatted as a numeric
string without hyphens. A data field named €˜ZipCode is assumed.
{QUOTE{SET Zip {MERGEFIELD ZipCode}}
{IF{Zip} 99999 {SET Zip {Zip \# "00000'-'0000"}}}
"{=-{=-{Zip}-Zip}/2 \# 00000;;}{=-({Zip}*(-1)-Zip)/2 \# ;-0000;}"}
or
{QUOTE{SET Zip «ZipCode»}
{IF{Zip} 99999 {SET Zip {Zip \# "00000'-'0000"}}}
"{=-{=-{Zip}-Zip}/2 \# 00000;;}{=-({Zip}*(-1)-Zip)/2 \# ;-0000;}"}

Notes:
1. The field brace pairs (ie '{ }') for the above examples are created via Ctrl-F9 - you can't simply type them or copy & paste them
from this message. Likwise, you can't type or copy & paste the chevrons (ie '« »') - they're part of the actual mergefields, which
you can insert from the mailmerge toolbar.
2. Although I've laid out the field codes with line feeds for readability, you can omit them if you prefer
3. The above examples also assume your Zip Code data are in a field named ZipCode - modify as need to suit your requirements.

--
Cheers
macropod
[Microsoft MVP - Word]


"N" wrote in message ...
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.

[Peter Jamieson]

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.