Why does your address show as '123 Main StCity, ST 12345' instead of two lines? Why does Alt+Enter seem to do nothing until you double-click the cell? Why does =CONCATENATE(A2,"\n",B2) just spit out a literal \n?
The answer is simple: Excel treats line breaks as *character codes*, not formatting. And unless you’ve enabled Wrap Text and used the right insertion method, Excel ignores them completely.
The Setup
You’re managing vendor contact info from a legacy CRM export. Column A has company names. Column B has full addresses — but they’re crammed into one field with commas separating street, city, state, and ZIP. You need each part on its own line *inside the same cell*, so printed reports stay readable and pivot tables don’t break.
| A1: Vendor | B1: Raw Address |
|---|---|
| Acme Corp | 789 Oak Ave, Portland, OR 97205 |
| Nexus Labs | 456 Pine St, Seattle, WA 98101 |
| Veridian Systems | 101 Elm Blvd, Austin, TX 78701 |
| Skyline Dynamics | 333 Cedar Ln, Denver, CO 80202 |
| TerraFusion Inc | 888 Birch Dr, Nashville, TN 37203 |
| Orion Group | 555 Spruce Ct, Miami, FL 33132 |
| Lumina Tech | 222 Maple Way, Atlanta, GA 30303 |
| StellarEdge Ltd | 999 Redwood Rd, San Francisco, CA 94103 |
| Voyant Solutions | 666 Sycamore Pl, Chicago, IL 60601 |
| Zephyr Partners | 111 Willow Ave, Boston, MA 02101 |
The Challenge
You need to replace each comma + space (", ") in B2:B11 with a true line break — not just wrap text, not just extra spaces. This isn’t about display only. It’s about *structural line separation* that survives copy-paste, exports to PDF, and works inside formulas like =LEN() or =SUBSTITUTE().
Most people try typing Enter while editing a cell. That just submits the formula and moves down. Others paste pre-formatted text and assume Wrap Text alone will fix it — it won’t. The line break character must be *embedded*. And if you use CHAR(10), it fails on Windows without enabling Wrap Text *first*.
Walking Through It
Step 1: Select B2. Press F2 to edit. Navigate to the comma after "Oak Ave". Delete ", " and press Alt+Enter. You’ll see the cursor drop one line. Do the same after "Portland". Press Enter to confirm.
Step 2: Now apply Wrap Text to B2. Go to Home → Wrap Text (or press Alt+H+W). Without this, Alt+Enter leaves invisible characters — no visual break.
Step 3: To automate across B2:B11, use SUBSTITUTE with CHAR(10). In C2, enter:=SUBSTITUTE(B2,", ",CHAR(10))
Then apply Wrap Text to C2:C11.
Before (B2): 789 Oak Ave, Portland, OR 97205
After (C2):
789 Oak Ave
Portland
OR 97205
| C1: Clean Address |
|---|
| 789 Oak Ave<div>Portland</div><div>OR 97205</div> |
| 456 Pine St<div>Seattle</div><div>WA 98101</div> |
| 101 Elm Blvd<div>Austin</div><div>TX 78701</div> |
| 333 Cedar Ln<div>Denver</div><div>CO 80202</div> |
| 888 Birch Dr<div>Nashville</div><div>TN 37203</div> |
Surprising tip: CHAR(13) (carriage return) works on Mac. CHAR(10) (line feed) works on Windows and web Excel. But in practice, use CHAR(10) everywhere — Excel normalizes it automatically. Don’t waste time checking OS.
The Result
Here’s the final cleaned column C, with Wrap Text applied and row height auto-adjusted:
| C1: Final Address |
|---|
| 789 Oak Ave<div>Portland</div><div>OR 97205</div> |
| 456 Pine St<div>Seattle</div><div>WA 98101</div> |
| 101 Elm Blvd<div>Austin</div><div>TX 78701</div> |
| 333 Cedar Ln<div>Denver</div><div>CO 80202</div> |
| 888 Birch Dr<div>Nashville</div><div>TN 37203</div> |
| 555 Spruce Ct<div>Miami</div><div>FL 33132</div> |
| 222 Maple Way<div>Atlanta</div><div>GA 30303</div> |
| 999 Redwood Rd<div>San Francisco</div><div>CA 94103</div> |
| 666 Sycamore Pl<div>Chicago</div><div>IL 60601</div> |
| 111 Willow Ave<div>Boston</div><div>MA 02101</div> |
What Could Go Wrong
Mistake #1: Forgetting Wrap Text
You inserted Alt+Enter or CHAR(10), but the cell still shows everything on one line. Check: Is Wrap Text enabled? If not, Excel renders line breaks as invisible. No error — just silence.
Mistake #2: Using =CONCATENATE with "\n"
Typing =CONCATENATE(A2,"\n",B2) inserts the literal characters \n — not a line break. Use CHAR(10) instead. Always.
Mistake #3: Applying CHAR(10) before setting column width
If column C is only 12 pixels wide, Excel won’t expand rows — even with Wrap Text on. Double-click the column divider (C/D) to auto-fit *after* applying CHAR(10) and Wrap Text.
| Method | Time for 10K rows | Accuracy | Difficulty |
|---|---|---|---|
| Manual Alt+Enter | ~22 min | 100% | Easy |
| SUBSTITUTE + CHAR(10) | ~8 sec | 100% | Medium |
| Power Query Replace | ~45 sec setup + 3 sec/run | 100% | Hard |
| Find & Replace (Ctrl+H) | ~2 min | 92%* | Easy |
*Fails if commas appear elsewhere (e.g., “Portland, OR” vs “Tech, Inc.”).
Next step: Open your sheet now. Try Alt+Enter in D1. Then type =SUBSTITUTE(B2,", ",CHAR(10)) in E2. Apply Wrap Text to E2:E11. Double-click column E’s right border. Done.
