Join a wrapped address line

Join the one line break your source marks as a wrap. Keep the rest.

What you'll make

In Excel: 12 Oak ¶ St ¶ Boston + break 1 becomes 12 Oak St ¶ Boston.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The address block. ¶ marks a line break."12 Oak¶St¶…"
2CHAR(10)The line break to look for."¶"
3" "A space to put in its place." "
4B8Which break to join. 1 is the first.1
5SUBSTITUTE( … )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.

  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 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.
  3. 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