When you use and Excel spreadsheet as a data file in a Word mail merge, formatting zip codes can sometimes make you want to tear all the hair out of your head.
This is a particularly annoying problem because most users assume they are solving the problem by correctly formatting zip codes in Excel. However, correctly formatted zip codes in Excel sometimes still arrive in Word without their leading zeroes.
Here is one way to fix the problem permanently:
- Start Word, and then open a new blank document.
- Go to Word Options
- In Word 2007, click the Office Button, and then click Word Options.
- In Word 2010/2013, click File, and then click Options.
- On the Advanced tab, go to the General section.
- Click to select the Confirm file format conversion on open check box, and then click OK.
- Start the mail merge and then select your recipients by navigating to the Excel spreadsheet that contains your data. Excel will display the following dialog:
- In the Confirm Data Source dialog box, click to select the Show all check box. Click MS Excel Worksheets via DDE (*.xls), and then click OK.
- In the Microsoft Excel dialog box, under Named or cell range, select the cell range or worksheet that contains the data that you want to use, and then click OK.
- Next, add merge fields to your main document (labels, envelopes, etc.). When you preview or finish the merge, the zip codes will format correctly with leading zeroes (the way they appeared in the Excel spreadsheet).
Some users have reported an error message when attempting to set up the DDE connection. The cause for the error can be a variety of reasons (such as a filtered spreadsheet or a conflict with a printer driver). If you cannot establish the DDE connection, there is an alternative method to force the leading zeroes in a zip code to appear in a mail merge. Check out this post:
Leading Zeroes Missing from Zip Codes in a Mail Merge: Another Option
Thank you – this was driving me crazy!
thank you. this never fails to stump me!
THANK YOU THANK YOU!!! I’m practically bald from trying to figure this out on my own. Good karma is on its way to you!
I am running Word 2013 with Windows 8.1. I tried the process above to fix my issue with missing leading zeroes on my zip codes. After Clicking MS Excel Worksheets via DDE on the Confirm Data Source box, I got the following error. “word could not re-establish ADDE connection to MSExcel to complete the current task. I also tried clicking on the ODBC option and that didn’t work either. I really would appreciate some help.
This process will not work if there is a filter set on the Excel spreadsheet. Do you have any filters set, by any chance? If so, just clear them in Excel, save and try again.
If you need to filter your recipients, you can do it from the Edit Recipients dialog box in Word after you start the merge.
I hope this helps. Let us know.
Cindy, I did some quick research, and in addition to a filter interfering with the DDE connection, this can also be caused by a conflict with a printer driver. If you have any printers installed that you no longer use, you might want to remove them, and also consider removing and reinstalling the default printer, to see if that solves the problem. Let us know if you get it resolved. If not, there is another way to fix the “missing zero problem” by editing field codes in Word. We will try to get that published soon.
Pingback: Leading Zeroes Missing from Zip Codes in a Mail Merge: Another Option | Notes from the Help Desk
Cindy, I just posted instructions on another way to get the leading zeroes to appear, if you cannot resolve the connection problem. Check out the post called, “Leading Zeroes Missing from Zip Codes in a Mail Merge: Another Option.”
When I attempted to change to DDE, it reordered the contents ofthe address box to add my fist column into my address (my first column is a category, not he first name) Any way to fix this? Thanks!
I found this way is the only thing that worked for me. The site link below explains, but this is the ginst of it:
“…In the case of a mail merge, the easiet way to maintain the zero is to format your column in Excel containing the zip code as a Special–>Zip Code format and then to save your Excel spreadsheet as either a Text (Tab delimited) (*.txt) or CSV (Comma delimited) (*.csv) file type. The latter of the two (.csv) requires that there are no commas within your data. What this process does is simply “flatten” the file, marrying or “melting” the two layers together (visualize your cheese being melted on your burger and you can no longer separate the two). Next, use your .txt or .csv version of the file as the data source for your Word merge….”
Site: http://www.hamilton.edu/its/its-newsletter-archive/september-2013/mail-merge-and-the-preservation-of-leading-zeros
Thank you. That was an easy solution.
I am sitting here working on merging addresses from my Excel file to Word and these zeroes show up in the zip code and I almost lost it until I remembered Google. I stumble on your article. I followed the steps and it worked like a charm. THANK YOU SO MUCH.
THANK YOU – many hours were wasted on this issue and Microsoft was no help!
THANK YOU !! Your 2 minute fix replaced my 1 hour search for a solution!!
wonderful help at the touch of the mouse!
this has driven me crazy for YEARS! Did not work for me:( I do not have the zero’s in my Excel though? When I type them in the field they just disappear? How to fix that issue!! ThNk you!!
Be sure you have formatted the zip codes in Excel properly: Right-click in the cell and select Format Cells… Click the Number tab, select the Special category, select Zip Code, and click OK.
thank you. you just saved my computer’s life. i no longer want to throw it out the window. and your fix was way easier than the other two posted!!!
THANK YOU SO MUCH!!!! I was literally going CRAZY trying to figure this out, almost considered just typing them in individually which would have taken FOREVER. GOD BLESS>
How about Microsoft just fixing it and having their “compatible” programs recognize each others formats!
Thank you so much. Very helpful
thank you! solved my problem!
I just tried this and it doesn’t work. I am using the new version of Excel .xlsx. I converted the file to .xls and neither version will open in WORD 2013.
Thank You!
THANK YOU! This is amazing. Such an easy fix when you know how. No more typing in the zero manually. You have made my day!
I found the easiest way to resolve my zip code dilemma is to right click in the zip merge field when in the mailings tab. select Toggle field codes. then type the following… { MERGEFIELD Zip \# 0####}
The MERGEFIELD code is good, but a better match is 00000. The # is an “optional-put a number there if there is one” that might be an issue.
I’ve been having the issue for years and found the remedies didn’t work for the standard “address block” merge field. I only have one address with the issue. Since my main concern was the appearance on the envelope and letter headings, not the way it’s displayed when printing the datasheet, I simply left a space after the state and then added the zip code in that cell and wiped it from the zip code cell. When the sheet’s printed, it “bleeds” over to the zip code space as it’s open so is displayed just not in the separate zip code cell. On the envelope and letter heading, it looks as it should. When you take it out of the formatted-for-zip code cell, the leading 0 merges along with the rest of the data and prints out normally.
It works, which is great, this has given me headaches from time to time, I hope this continues to work.
However, I’m finding I do not have an option to select one worksheet within a workbook. The merge no longer gives me an option to select a worksheet, it pulls the “spreadsheet” but only the data from the first worksheet. Only options are to type in a cell range (no option for specific worksheet) or entire spreadsheet. So, now I have to create a separate workbook for each worksheet….any help?
Thank You!!!
Just tested the options of saving as a CSV and TXT file and then running the merge from those files and both worked.
Would very much recommend using the TXT option as it is not unusual to have commas in the addresses, and those will end up separating your data unintentionally.
Tried the DDE option described and was unable to get WORD 2016 to get along with EXCEL 2016 and see the leading zeros in the zip.
Happy New Year to all –
Finally! A working solution. Save Excel source as TEXT(tab delimited)
I almost gave up…but thanks to your easy to read step by step instructions-I got it! Never again putting 0’s in front of all my zips individually! THANK YOU!!