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?
 

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,310
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
75,361
Office Version
365
Platform
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,310
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,081,415
Messages
5,358,527
Members
400,501
Latest member
price83

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top