Combine ZIP and +4 numbers as text

Turn 1002 and 0 into 01002-0000, so a zero extension isn't lost.

What you'll make

In Excel: 1002 + 0 becomes 01002-0000.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TEXT(A8,"00000")Writes the ZIP as five digits, zeros in front."02108"
2IF(B8="",…)Adds a hyphen and four digits, if there's a +4."02108-1234"

Read it left to right. A +4 of 0 becomes 0000. An empty +4 adds nothing.

=TEXT(A8,"00000")&IF(B8="","","-"&TEXT(B8,"0000"))

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own addresses. Paste ZIP numbers and +4 numbers into the green columns of the Template tab. Leave the +4 empty when there isn't one.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use this only on columns you know hold U.S. ZIPs and +4 parts. It can't tell if a ZIP is real.

Microsoft's help: TEXT function