Read a line by number, keeping blanks

An empty second line still counts, so line 3 stays line 3.

What you'll make

In Excel: 12 Oak St ¶ ¶ Boston, MA 02108 + 3 becomes Boston, MA 02108.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1CHAR(10)&A8The block with a line break in front. ¶ marks one."¶12 Oak St¶¶Boston…"
2TEXTAFTER( … ,CHAR(10),B8)Keeps what comes after line break number B8."Boston, MA 02108"
3TEXTBEFORE( … &CHAR(10),CHAR(10))Stops at the next line break."Boston, MA 02108"
4IFERROR( … )Says Review when the block has no such line."Boston, MA 02108"

Parts 2 and 3 work from the inside out. Empty lines count.

=IFERROR(TEXTBEFORE(TEXTAFTER(CHAR(10)&A8,CHAR(10),B8)&CHAR(10),CHAR(10)),"Review missing address-line position")

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 and the line number you want into the green columns of the Template tab. The lines fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

An empty line gives an empty cell. That is the right result, not a mistake.

Microsoft's help: TEXTAFTER function