Cleaning up Zip Codes for Mailings

Have you run Mail Merges with data from your donor management system (like DPO) and had trouble formatting your Zip Code fields? Rhonda Parker of Ramah New England offered a
couple of tips for cleaning up this data. Note that you can find steps for preparing a Mail Merge on our website.

Removing extra dashes from Zip Codes

When zip codes are exported from a database, it sometimes includes a dash at the end (in casethe 9 digit zip code is available). To remove those dashes in your Excel file before merging with your Word document (note that this process will lose the extra 4 digits in any 9 digit zip codes from your database):

1. Add a new column in the spreadsheet to the right of the Zip Code column.
2. Click on the Zip Code column
3. Select Data -->Text to Columns
4. Select "Delimited"
5. Click Next
6. Check the "Other" checkbox; enter a dash in the box next to "Other"
7. Click Next
8. Ensure the Zip Code field is highlighted in the Convert Text to Columns Wizard window
9. Select "Text" as the data type (this will ensure you don't lose the leading zeros in your zip codes)
10. Click Finish

You should now have the 5 digit zip codes in the zip code column. The extra column you added to the right will hold the extra 4 digits for any person who had it in their record; it can be deleted before merging with Word.

Adding leading zeros to Zip Codes

When zip codes are exported from a database, the leading zeros are often dropped. To add these zeros back in (and make sure they aren't dropped again when the data is merged with Word!), you'll want to change the format of the Zip Code field to Text (with 5 characters):

1. Add a new column in the spreadsheet to the right of the Zip Code column.
2. In the field to the right of the first zip code in the list, type the following formula: =TEXT(<x>,"00000"), where x is the cellname of the first zip code in the list. For example, if the Zip Code column is column F, and the first row of data is in row 2, you will type =TEXT(F2,"00000"). This tells Excel that you want the Zip Code from Cell F2 formatted as Text, always with 5 characters.
3. Press <Enter>. The zip code in the new column will display properly.
4. Copy this formula to all cells in this new column. Start by clicking on the properly displayed zip code.
5. Move your mouse pointer until it is on the lower right hand corner of the cell. It will look like a small black plus sign (+).
6. Hold down the mouse button and drag down until you are at the cell next to the last zip code in the original Zip Code column.
7. Release the mouse button. The formula is now copied to all selected cells.
8. To store the new values (and not just this new formula), select the new column (click on the column letter on the top of the column).
9. Select Format --> Cells
10. On the Number tab, choose Text.
11. Click OK to change the format of the new column to Text instead of Number.
12. Select the new column again.
13. Select Edit --> Copy to copy the new zip codes
14. Select the original Zip Code column.
15. Select Edit --> Paste Special.
16. Choose "Values" on the Paste Special Dialog Box.
17. Click OK
18. Select the new column (with the formulas).
19. Select Edit --> Delete to delete the entire column.
20. Add the column header "Zip" or "Zip Code" to the updated Zip Code column.

You are now done and ready to merge this spreadsheet into your Word file. Any zip codes with leading zeros will print those zeros.