Remove a NULL address line
Drop a line that says only NULL. NULL inside a street name stays.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8 | The address. ¶ marks a line break. | "12 Oak St¶NULL¶Bo…" |
| 2 | CHAR(10)&"NULL"&CHAR(10) | A line that says only NULL. | "¶NULL¶" |
| 3 | CHAR(10) | One line break to put in its place. | "¶" |
| 4 | SUBSTITUTE( … ) | 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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own addresses. Paste address blocks into the green column of the Template tab. Each fixed block fills in by itself.
- 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
OTHER ROUTES YOU MIGHT LIKE

Join a wrapped address line
12 Oak ¶ St ¶ Boston + break 1 → 12 Oak St ¶ Boston
Get the template →
Read a line by number, keeping blanks
12 Oak St ¶ ¶ Boston, MA 02108 + 3 → Boston, MA 02108
Get the template →
Calculate remaining label lines
6-line label, 3 address + 1 program → 2 lines left
Get the template →
Remove the empty line when there's no apartment
No apartment → no empty address line
Take this route →