Extract attention text from an ATTN line
Turn an address block that starts ATTN Ava Reed into Ava Reed.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | LEFT(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 |
| 3 | LEFT(A8,FIND(CHAR(10),A8&CHAR(10))-1) | Keeps the first line only. ¶ marks a line break. | "ATTN Ava Reed" |
| 4 | MID( … ,6,LEN(A8)) | Drops ATTN and its space. | "Ava Reed" |
| 5 | IF( … ) | 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.
- 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. The attention names fill in by themselves.
- 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
OTHER ROUTES YOU MIGHT LIKE

Read a mailstop from a company field
Acme Co. | MAILSTOP: 4B-210 → 4B-210
Get the template →
Recover the firm from a mailstop field
Acme Co. | MAILSTOP: 4B-210 → Acme Co.
Get the template →
Add one ATTN prefix without doubling it
Ava Reed or ATTN Ava Reed → ATTN Ava Reed
Get the template →
Put the street back in an Address Block
Ava Reed, Boston → Ava Reed, 12 Main St, Boston
Take this route →