Mail merge prints zip as 0 instead of correct code

[Penelope]

When the mail merge is completed for either envelopes, labels or letters some
of the zip codes are converted to a 0 instead of the complete 5 digit code.
The cells have been formated as number - special - zip code.

 

[Peter Jamieson]

Although you have done this

The cells have been formated as number - special - zip code.

My guess is that some of the ZIPs are still actually "numbers formatted
as text" rather than "genuine numbers." If the first 8 rows in the
column are genuine numbers, then when Word gets the data, it will set
any text values to 0 (even if they look like numbers).

You /may/ be able to see which are texts by making the column wider than
about 5 characters, and looking down the column. If the ZIP is
left-justified, it suggests that it a number formatted as text. If it is
right-justified, it suggests that it is actually a number looking at the
column. If there are just a few numbers formatted as text, you can
consider re-applying the formatting (as necessary) /then re-entering the
number/ - after that, it should be a "genuine number" However, I
wouldn't want to rely completely on this approach.

Another possibility is to format the column as "General" via Data|Text
to Columns...
- select the column
- select the Data|Text to Columns... menu option (in Word 2007, Data
tab->Data Tools group->Text to columns)
- click Next through the wizard until you reach Step 3 of 3, then
select General as the Column Data Format.
- complete the wizard.

If necessary, re-apply the formatting you want to the column.

If your ZIPs are "genuine numbers", in Word you will need to modify the
mergefield to format thtem correctly, e.g., use Alt-F9 to view the field
codes, look for the zip field (let's say it is "myzip") and change

{ MERGEFIELD myzip }
to
{ MERGEFIELD myzip \#00000 }

There is an explanation of some of this stuff at
http://tips.pjmsn.me.uk/t0003.htm


Peter Jamieson

http://tips.pjmsn.me.uk

 

[macropod]

Hi Penelope,

If you find the problem Peter has alluded to, you can overcome the problem by selecting the Zipcode cells in Excel and use
Find/Replace, with a hyphen as the Find character and nothing for the Replace character. This will turn all the Zipcode text strings
into Zipcode numbers.

The only issue you might then have is that your mailmerge will omit the hyphens for the 5+4 Zipcodes! If so, you can deal with this
by adding some logic to the Word document. A suitable field coding for this is:
{SET Zip {MERGEFIELD ZipCode}}{IF{Zip}> 99999 {Zip \# "00000'-'0000"} {Zip \# "00000"}}
where 'ZipCode' is the name of your Zip Code mergefield.

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

 

[Peter Jamieson]

If you find the problem Peter has alluded to, you can overcome the

problem by selecting the Zipcode cells in Excel and use Find/Replace,
with a hyphen as the Find character and nothing for the Replace
character. This will turn all the Zipcode text strings into Zipcode
numbers.

Just as a clarification,
a. if there are 9-digit ZIPs with hyphens, and the hyphens are there
because they have been entered in the ZIP, then yes, you would have to
remove the hyphens in the way you suggest. And AFAICS that would
actually result in the 5-4 ZIP being converted to a genuine number that
would then be displayed with whatever formatting has been applied to the
cell.
b. However, if there are also 5-digit ZIPs that were entered at a
point when the formatting of the cell was Text, then there won't be a
hyphen to find, nothing will change and the approach I suggested should
fix that (although perhaps some other substitution would also do it).

I dealt with option (b) primarily because that was the one that appeared
to be described in the original post.

Peter Jamieson

http://tips.pjmsn.me.uk