Reconcile two ZIPs without hiding a +4

Keep the fuller ZIP when two checked sources agree. Flag any conflict.

What you'll make

In Excel: 02108 + 02108-1234 becomes 02108-1234.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1LEFT(A8,5)<>LEFT(B8,5)Checks whether the first five digits differ.Same, 02108
2IF(AND( … ))Checks whether both have a different +4.No, only one has a +4
3IF(LEN(A8)=10,A8,B8)Uses source 1 if it has the +4, else source 2."02108-1234"
4IF( … )Flags a conflict, otherwise uses part 3."02108-1234"

Read it left to right. A ZIP+4 like 02108-1234 is 10 characters, so LEN = 10 means it has a +4.

=IF(LEFT(A8,5)<>LEFT(B8,5),"Review ZIP5 mismatch",IF(AND(LEN(A8)=10,LEN(B8)=10,A8<>B8),"Review ZIP+4 mismatch",IF(LEN(A8)=10,A8,B8)))

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 the ZIP from each source into the two green columns of the Template tab. The reconciled ZIP fills in by itself.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Fix flagged rows by hand. The formula doesn't decide which source is right or make up a missing +4.

Microsoft's help: IF function