Excel to Word, using mail merge drops the leading 0 in zip codes. How to preserve 0's in 5 digit zip codes, when leading zero?

exceltm

New Member
Joined
Apr 5, 2010
Messages
32
I have 5 digit and 5+4 = 9 digit zip codes in Excel. Some start with 0 such as 01122. Using the mail merge wizard, the zip codes that begin with zero are getting dropped, which is not what I want. I dont want it to convert a zip from 01122 to 1122, because a zip code should include the leading zero, not drop it.

Ive tried in MS Excel formatting the fields to custom zip of 5 or 9 digits and they still get the leading zeros dropped after the mail merge conversion. What should I be doing differently?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

fletcherj23

New Member
Joined
Nov 13, 2012
Messages
16
Unfortunately I don't see a Mail Merge on my end, but you could try the Custom scroll down to the bottom and use the 00000-0000 format.
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,567
The following Word field code 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 (eg because a db only accepts Zip Codes in the 5+4-digit format) or is formatted as a 5-digit or 9-digit 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;}"}

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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
In Word you can use switches to format the fields.

For example if the field for zip codes was called ZipCode.

{MERGEFIELD ZipCode \#"00000-0000"}

You can add the switch by right clicking the field and selecting Toggle field codes.
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,567

ADVERTISEMENT

In Word you can use switches to format the fields.

For example if the field for zip codes was called ZipCode.

{MERGEFIELD ZipCode \#"00000-0000"}

You can add the switch by right clicking the field and selecting Toggle field codes.
That gives the wrong result for 5-digit Zip codes (eg a Zip code of 01234 would be rendered 00000-1234)
 

exceltm

New Member
Joined
Apr 5, 2010
Messages
32
Unfortunately I don't see a Mail Merge on my end, but you could try the Custom scroll down to the bottom and use the 00000-0000 format.

This doesn't work, because after trying both CUSTOM and SPECIAL, both convert all 5 digit zips within Excel from 12345 to 00001-2345, even before trying to convert them into MS Word. Right now, they are formatted as SPECIAL - ZIP CODE.
 

exceltm

New Member
Joined
Apr 5, 2010
Messages
32
I should hit CTRL F9 and insert all 4 of your lines? I tried that and couldnt get it to work.
1 It still incorrectly changed as an example, 01234 to 1234 as the zip
2 It listed all those formatting marks

Here is the result as shown on my letter:
100 Danbury Road
Ridgefield CT
{SET Zip {MERGEFIELD 6877}}
{IF{Zip}> 99999 {SET Zip {Zip \# "00000'-'0000"}}}
"{=-{=-{Zip}-Zip}/2 \# 00000;;}{=-({Zip}*(-1)-Zip)/2 \# ;-0000;}"}


The following Word field code 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 (eg because a db only accepts Zip Codes in the 5+4-digit format) or is formatted as a 5-digit or 9-digit 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;}"}

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

Forum statistics

Threads
1,136,844
Messages
5,678,086
Members
419,741
Latest member
BKN336

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top