Join a ZIP and an optional +4 extension

Turn 02108 and 1234 into 02108-1234. No extension, no hyphen.

What you'll make

In Excel: 02108 + 1234 becomes 02108-1234.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1B8=""Checks whether the extension is empty.Not empty
2A8The ZIP alone, when there's no extension.Not used for row 8
3A8&"-"&B8The ZIP, a hyphen, then the extension."02108-1234"
4IF( … )Uses part 2 or part 3."02108-1234"

The hyphen only appears when there is an extension. Both cells stay text, so zeros stay.

=IF(B8="",A8,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 ZIP codes and +4 extensions into the green columns of the Template tab. Leave the extension empty when there isn't one.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Paste ZIPs and extensions as text so zeros like 02108 and 0021 stay. The formula doesn't add missing zeros.

Microsoft's help: IF function