Mail Merge and the Preservation of Leading Zeros
By Maureen Scoones
During the past few weeks, I fielded a number of calls asking the same question: I’m doing a mail merge and my zip codes are missing the first zero, how do I fix that? Hopefully the information below will help you visualize what is happening and why, and more importantly, how to fix it.
When doing a mail merge in Word and using data from Excel, why do the zeros drop?
You need to think of your data as living in layers within Excel. The bottom layer is the data and the upper layer is the formatting. Visualize a burger with cheese. Your data is the burger and the formatting or “topping” is the cheese. OK, you've got the visual, now what? When Word and Excel exchange numbers, they are doing just that, exhanging numbers, no formatting or “toppings.” Meaning Word knows about the burger, but left the cheese behind. If you click in a cell that has a zip code that starts with a zero, e.g. 01234, you'll notice the formula bar will only show 1234. Mathematically the zero, also known as the leading zero, has no meaning. Notice in the screen shot below that even though the number has the “Special” format applied, in this case zip code, Excel still only shows 1234 in the formula bar where the actual cell shows 01234.
How do you keep the zeros?
In the case of a mail merge, the easiest 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. There are even more options (and more options, trust me when I say there are even more) to handle the leading zeros, but for most uses you’ll have, these can be more trouble than they are worth.