Remove a NULL address line

Drop a line that says only NULL. NULL inside a street name stays.

What you'll make

In Excel: 12 Oak St ¶ NULL ¶ Boston becomes 12 Oak St ¶ Boston.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The address. ¶ marks a line break."12 Oak St¶NULL¶Bo…"
2CHAR(10)&"NULL"&CHAR(10)A line that says only NULL."¶NULL¶"
3CHAR(10)One line break to put in its place."¶"
4SUBSTITUTE( … )Swaps the NULL line for a single line break."12 Oak St¶Boston, …"

Only a whole line of NULL in capitals is removed.

=SUBSTITUTE(A8,CHAR(10)&"NULL"&CHAR(10),CHAR(10))

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 address blocks into the green column of the Template tab. Each fixed block fills in by itself.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

A NULL on the first or last line stays, and so does one of two NULL lines in a row. Check those rows by hand.

Microsoft's help: SUBSTITUTE function