Join a wrapped address line
Join the one line break your source marks as a wrap. Keep the rest.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8 | The address block. ¶ marks a line break. | "12 Oak¶St¶…" |
| 2 | CHAR(10) | The line break to look for. | "¶" |
| 3 | " " | A space to put in its place. | " " |
| 4 | B8 | Which break to join. 1 is the first. | 1 |
| 5 | SUBSTITUTE( … ) | Swaps only that one break for a space. | "12 Oak St¶…" |
The other line breaks stay as they are.
=SUBSTITUTE(A8,CHAR(10)," ",B8)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. Put the number of the break to join next to each. The fixed blocks fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Count the breaks before you paste. A number past the last break leaves the block as it was.
Microsoft's help: SUBSTITUTE function
OTHER ROUTES YOU MIGHT LIKE

Read a line by number, keeping blanks
12 Oak St ¶ ¶ Boston, MA 02108 + 3 → Boston, MA 02108
Get the template →
Remove a NULL address line
12 Oak St ¶ NULL ¶ Boston → 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 →