Add one ATTN prefix without doubling it

Turn Ava Reed into ATTN Ava Reed, and leave ATTN Ava Reed with only one ATTN.

What you'll make

In Excel: Ava Reed or ATTN Ava Reed becomes ATTN Ava Reed.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TRIM(A8)=""Checks whether the name cell is empty.Not empty
2LEFT(UPPER(TRIM(A8)),5)="ATTN "Checks if it already starts with ATTN and a space.No
3"ATTN "&TRIM(MID(TRIM(A8),6,LEN(A8)))Keeps one ATTN and the name after the old one.Not used for row 8
4"ATTN "&TRIM(A8)Adds ATTN and a space before the name."ATTN Ava Reed"
5IF( … )Shows nothing when empty, otherwise part 3 or part 4."ATTN Ava Reed"

Read it left to right. Capitals don't matter for the check, so attn and Attn count too.

=IF(TRIM(A8)="","",IF(LEFT(UPPER(TRIM(A8)),5)="ATTN ","ATTN "&TRIM(MID(TRIM(A8),6,LEN(A8))),"ATTN "&TRIM(A8)))

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 names. Paste each attention name into the green column of the Template tab. The attention lines fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Only ATTN with a space is spotted. Fix ATTENTION or Attn: by hand first.

Microsoft's help: IF function