How to Prepare Your Zip Codes for Mailing Labels

Clients call me up in a panic. “Danny, we are sending out a direct mail piece and Excel has screwed up our Zip Codes – they only contain 4 numbers! The Post Office can’t deliver the mailing piece this way. Can you help us?”

Yes I can!

The problem is not with Excel. The problem is not choosing the correct formatting for the Columns / Fields that contain Zip Codes or Postal Codes.

At the risk of jeopardizing some lucrative consulting fees, I will demonstrate how to solve this problem in this short Excel Video Tutorial.

Here are the steps to follow in this Excel Video Lesson:

When you have control over your data, pre-format your Zip Codes column using the “Formant Cells, Number, Special, Zip Code” format.
If your data contains cells with both 4 and 5 digit Zip Codes, use this formula:
=IF(A2<=9999, 0&A2, A2)
Now, you will see some cells formatted as “Text” – aligned to the Left side of the cell and other cells formatted as “Numbers” – aligned to the Right side. Take the next step:
First, Copy the data (the cells w/ the =IF() Function) and choose Paste Special, Values to return the results of the formulas.
With the cells still selected choose, Data, Text-to-Columns and choose “Fixed Length.” After previewing your data, choose the “Format as Text” button and Finish the Wizard.