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]

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?

 

[fletcherj23]

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]

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]

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]

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)

 

[saelageonzon]

You can try using =TEXT(A1,"00000") for those zipcodes with dropped zeroes.

 

[Macropod]

You can try using =TEXT(A1,"00000") for those zipcodes with dropped zeroes.

That won't help, as the issue is with Word, not Excel.

 

[exceltm]

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]

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.