Read a line by number, keeping blanks
An empty second line still counts, so line 3 stays line 3.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | CHAR(10)&A8 | The block with a line break in front. ¶ marks one. | "¶12 Oak St¶¶Boston…" |
| 2 | TEXTAFTER( … ,CHAR(10),B8) | Keeps what comes after line break number B8. | "Boston, MA 02108" |
| 3 | TEXTBEFORE( … &CHAR(10),CHAR(10)) | Stops at the next line break. | "Boston, MA 02108" |
| 4 | IFERROR( … ) | 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.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- 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.
- 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
OTHER ROUTES YOU MIGHT LIKE

Remove a NULL address line
12 Oak St ¶ NULL ¶ Boston → 12 Oak St ¶ Boston
Get the template →
Join a wrapped address line
12 Oak ¶ St ¶ Boston + break 1 → 12 Oak St ¶ Boston
Get the template →
Calculate remaining label lines
6-line label, 3 address + 1 program → 2 lines left
Get the template →
Put the street back in an Address Block
Ava Reed, Boston → Ava Reed, 12 Main St, Boston
Take this route →