Extract attention text from an ATTN line

Turn an address block that starts ATTN Ava Reed into Ava Reed.

What you'll make

In Excel: ATTN Ava Reed ¶ 12 Oak St becomes Ava Reed.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1LEFT(UPPER(A8),5)<>"ATTN "Checks the block doesn't start with ATTN and a space.No
2"Review missing leading ATTN line"What the cell says with no ATTN line.Not used for row 8
3LEFT(A8,FIND(CHAR(10),A8&CHAR(10))-1)Keeps the first line only. ¶ marks a line break."ATTN Ava Reed"
4MID( … ,6,LEN(A8))Drops ATTN and its space."Ava Reed"
5IF( … )Uses part 2 with no ATTN line, otherwise part 4."Ava Reed"

Parts 3 and 4 work from the inside out. ATTN can be in any case.

=IF(LEFT(UPPER(A8),5)<>"ATTN ","Review missing leading ATTN line",MID(LEFT(A8,FIND(CHAR(10),A8&CHAR(10))-1),6,LEN(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 addresses. Paste address blocks into the green column of the Template tab. The attention names fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

This copies only the attention name. The block keeps its ATTN line, so remove that line yourself if you need to.

Microsoft's help: MID function