Keep number and text ZIPs as text

Turn the number 2108 into 02108, and keep text ZIPs like 01002-4410 as they are.

What you'll make

In Excel: 2108 becomes 02108; 01002-4410 stays as it is.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8=""Checks whether the cell is empty.No, it has a number
2ISNUMBER(A8)Checks whether the ZIP is a number.Yes, 2108
3TEXT(A8,"00000")Adds zeros in front until there are five."02108"
4IF(ISNUMBER( … ) … )Uses part 3 for a number, or keeps text as it is."02108"
5IF( … )Leaves an empty cell blank."02108"

Read it left to right. Text ZIPs pass through unchanged, so only numbers get their zeros back.

=IF(A8="","",IF(ISNUMBER(A8),TEXT(A8,"00000"),A8))

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 your ZIPs, numbers or text, into the green column of the Template tab. The ZIP codes fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Paste only ZIPs you've already checked. The formula puts zeros back on numbers but doesn't fix text.

Microsoft's help: TEXT function