Turn backslash-n into address lines

Turn an exported 12 Oak St\nBoston, MA 02108 into two real address lines.

What you'll make

In Excel: 12 Oak St\nBoston, MA 02108 becomes two address lines.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The address, with the typed text \n between its lines."12 Oak St\nBoston…"
2CHAR(92)&"n"A backslash, which is CHAR(92), then a small n."\n"
3CHAR(10)A real line break. ¶ marks it."¶"
4SUBSTITUTE( … )Swaps every typed \n for a real line break."12 Oak St¶Boston…"

Read it left to right. Everything else in the cell stays as it is.

=SUBSTITUTE(A8,CHAR(92)&"n",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 exported addresses into the green column of the Template tab. The address lines fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use this only when every \n in your list means a new line. Turn on Wrap Text where you paste the results.

Microsoft's help: SUBSTITUTE function