Restore a five-digit ZIP from a number

Turn a ZIP that lost its zero, like 2108, back into the text 02108.

What you'll make

In Excel: 2108 becomes text 02108.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The ZIP as a number, with its zero lost.2108
2"00000"The pattern: always five digits."00000"
3TEXT( … )Adds zeros in front until there are five."02108"

The result is text, so Excel keeps the leading zero.

=TEXT(A8,"00000")

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

Before you send

Use this only on a column you know holds five-digit U.S. ZIPs. It can't tell if a ZIP is real.

Microsoft's help: TEXT function