Review out-of-range ZIP numbers

Turn 2108 into 02108, but flag negative, decimal or six-digit numbers.

What you'll make

In Excel: 2108 becomes 02108; 106103 becomes Review ZIP.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8>=0Checks the number isn't below zero.Yes, 2108
2A8<100000Checks it has five digits or fewer.Yes, 2108
3A8=INT(A8)Checks it's a whole number.Yes, 2108
4TEXT(A8,"00000")Adds zeros in front until there are five."02108"
5IF(AND( … ) … )Uses part 4 if all three checks pass."02108"

Read it left to right. AND needs all three checks to pass; otherwise the cell says Review ZIP.

=IF(AND(A8>=0,A8<100000,A8=INT(A8)),TEXT(A8,"00000"),"Review ZIP")

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

This checks only the size of the number, not whether the ZIP is real. 00000 passes but isn't a real ZIP.

Microsoft's help: INT function