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:

  1. When you have control over your data, pre-format your Zip Codes column using the “Formant Cells, Number, Special, Zip Code” format.
  2. If your data contains cells with both 4 and 5 digit Zip Codes, use this formula:
  3. =IF(A2<=9999, 0&A2, A2)
  4. 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:
  5. First, Copy the data (the cells w/ the =IF() Function) and choose Paste Special, Values to return the results of the formulas.
  6. 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.

Find the Excel Training Video that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn


  1. […] Solve your Zip Code problems when preparing mailing lists […]

Speak Your Mind