Choose an HC ZIP+4 from box or route

A highway contract (HC) box uses its box-range ZIP+4, never the route's.

What you'll make

In Excel: HC 1 BOX 120 becomes 02108-4410 (box range, not route).

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8=""Checks whether the HC box is empty.No, HC 1 BOX 120
2IF(B8="", … ,B8)No box: the route ZIP+4, or a flag if missing.Not used for row 8
3IF(C8="", … ,C8)A box: its box-range ZIP+4, or a flag if missing."02108-4410"
4IF( … )Uses part 2 with no box, otherwise part 3."02108-4410"

Read it left to right. A box with no box-range ZIP+4 is flagged, never given the route's.

=IF(A8="",IF(B8="","Review: missing route ZIP+4",B8),IF(C8="","Review: missing box ZIP+4",C8))

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. On the Template tab, paste each HC box, the route ZIP+4 and the box-range ZIP+4 into the green columns. Leave an HC box empty if there isn't one. The postal ZIP+4s fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Look up both ZIP+4s by the real route and box range first. This only picks between them.

Microsoft's help: IF function