Turn HTML br tags into address lines
Turn an exported 12 Oak St<br>Boston, MA 02108 into two real address lines.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8 | The address, with web code between its lines. | "12 Oak St<br>Boston…" |
| 2 | SUBSTITUTE( … "<br>" … ) | Swaps each <br> for a real line break. ¶ marks it. | "12 Oak St¶Boston…" |
| 3 | SUBSTITUTE( … "<br/>" … ) | Swaps each <br/> for a line break. | "12 Oak St¶Boston…" |
| 4 | SUBSTITUTE( … "<br />" … ) | Swaps each <br /> for a line break. | "12 Oak St¶Boston…" |
Each bracket wraps the one above it, so Excel works from the inside out.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A8,"<br>",CHAR(10)),"<br/>",CHAR(10)),"<br />",CHAR(10))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 exported addresses into the green column of the Template tab. The address lines fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Only lowercase <br>, <br/> and <br /> are changed. Other web code stays as it is, so check those rows. Turn on Wrap Text where you paste the results.
Microsoft's help: SUBSTITUTE function
OTHER ROUTES YOU MIGHT LIKE

Suppress a line 2 that repeats line 1
12 Oak St + 12 Oak St → line 2 blank
Get the template →
Fix mixed line breaks in address blocks
12 Oak St [Windows break] Boston → 12 Oak St ¶ Boston
Get the template →
Turn backslash-n into address lines
12 Oak St\nBoston, MA 02108 → two address lines
Get the template →
Put the street back in an Address Block
Ava Reed, Boston → Ava Reed, 12 Main St, Boston
Take this route →