Fix mixed line breaks in address blocks

Turn Windows and old Mac line breaks into the ones Excel uses.

What you'll make

In Excel: 12 Oak St [Windows break] Boston becomes 12 Oak St ¶ Boston.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The address. ¶ marks a line break."12 Oak St¶Boston, …"
2SUBSTITUTE( … CHAR(13)&CHAR(10) … )Swaps a Windows line break for Excel's kind."12 Oak St¶Boston, …"
3SUBSTITUTE( … CHAR(13) … )Swaps any hidden return left for a line break."12 Oak St¶Boston, …"

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

=SUBSTITUTE(SUBSTITUTE(A8,CHAR(13)&CHAR(10),CHAR(10)),CHAR(13),CHAR(10))

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. Each fixed block fills in by itself.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

This only fixes the line breaks. Spaces, empty lines and the address itself stay as they are.

Microsoft's help: SUBSTITUTE function