Get the name from the first line of a cell

Turn a name with an address under it into Ava Reed.

What you'll make

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

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TEXTBEFORE(A8,CHAR(10))Takes the first line. ¶ marks a line break."Ava Reed"
2SUBSTITUTE( … ,CHAR(13),"")Removes a hidden return left by some files."Ava Reed"
3TRIM( … )Removes any spaces left at the ends."Ava Reed"
4TRIM(A8)With no line break, keeps the whole cell.Not used for row 8
5IFERROR( … )Uses part 4 when there is no line break."Ava Reed"

Each bracket wraps the one above it, so Excel works from the inside out.

=IFERROR(TRIM(SUBSTITUTE(TEXTBEFORE(A8,CHAR(10)),CHAR(13),"")),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 names with the formula already in.
  2. Use your own names. Paste cells with the name on the first line into the green column of the Template tab. Each name fills in by itself.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

The whole first line counts as the name. If a first line also holds a title or company, check those rows by hand.

Route: Get names ready for the letter

Microsoft's help: TEXTBEFORE function