Suppress a line 2 that repeats line 1

Leave line 2 empty when it only repeats line 1, like 12 Oak St.

What you'll make

In Excel: 12 Oak St + 12  Oak  St becomes line 2 blank.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1UPPER(TRIM(A8))=UPPER(TRIM(B8))Checks whether line 2 is the same as line 1.Match
2TRIM(B8)Line 2 without extra spaces.Not used for row 8
3IF( … )Blank when they match, otherwise the clean line 2.""

Read it left to right. Capitals and extra spaces don't matter in part 1, so 12 OAK ST matches 12 Oak St.

=IF(UPPER(TRIM(A8))=UPPER(TRIM(B8)),"",TRIM(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 line 1 into column A and line 2 into column B of the Template tab. Clean line 2 fills in by itself.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Only an exact repeat turns blank. A line 2 with a different unit stays, so check near repeats by hand.

Microsoft's help: IF function