Yes, you can add text before existing content in Excel. But if you’re reaching for CONCATENATE or typing ="ID-"&A2 every time, you’re adding 3 extra seconds per cell—and that adds up fast when your sales team drops a 12,000-row lead list at 4:58 PM.
The Setup
We just got a raw export from our CRM—10 rows of contact data. No prefixes. No consistency. Marketing needs every email tagged with "MKT-" and every ID prefixed with "CUST-" before uploading to HubSpot. The sheet lives in Sheet1, with headers in row 1 and data starting at A2.
| A: Full Name | B: Email | C: Customer ID | D: Last Contact |
|---|---|---|---|
| Sarah Chen | sarah@acmecorp.com | 78942 | 2024-03-15 |
| James Rivera | jrivera@techflow.io | 10233 | 2024-04-02 |
| Priya Mehta | priya@nexgenlabs.co | 55601 | 2024-02-28 |
| Marcus Bell | mbell@veridion.ai | 99204 | 2024-05-11 |
| Lena Kim | lena@stratosdev.com | 33771 | 2024-01-22 |
| Diego Torres | dtorres@bluecore.net | 88405 | 2024-03-30 |
| Anya Patel | anya@cloudspire.org | 22199 | 2024-04-18 |
| Tariq Hassan | t.hassan@optivolt.co | 66513 | 2024-02-14 |
| Nina Wu | nina.wu@quantumreach.net | 44882 | 2024-05-05 |
| Eliot Frost | efrost@novarise.co | 11327 | 2024-04-26 |
The Challenge
Marketing asked for two things: prepend "MKT-" to each email in column B, and "CUST-" to each Customer ID in column C. Simple, right? Except—column C has numbers (78942), not text. And some emails already have "MKT-" from last month’s test batch. You can’t just slap on another prefix without checking first. Also, this sheet gets updated daily. If you hardcode formulas in column E and F, someone will inevitably paste over them—or worse, copy-paste values and lose the logic.
Here’s what makes it tricky: Excel won’t let you edit multiple cells *in place* with a formula. You need a new column, then replace—but only after validating. And if you use =CONCATENATE("MKT-",B2), you’ll get #VALUE! if B2 is blank or contains an error. That’s how you discover at 5:15 PM that row 4,321 broke the whole import.
Walking Through It
Start in cell E2. Type: =IF(B2="","", "MKT-"&B2). Press Enter. Then select E2, grab the fill handle (small square bottom-right corner), and double-click—it auto-fills down to E11. That’s Alt+Enter for line breaks? No—that’s Ctrl+Enter for filling selected cells. But double-click is faster here.
Now for column C. In F2, enter: =IF(C2="","", "CUST-"&TEXT(C2,"0")). Why TEXT(C2,"0")? Because without it, Excel converts 78942 to "CUST-78942"—fine—but if C2 holds 10233.00 or 55601.5, you’ll get unwanted decimals. TEXT() forces clean integer output. This is the counterintuitive bit: prepending text to numbers isn’t about &—it’s about controlling number formatting *first*.
| B: Email (before) | E: Email (after) | C: Customer ID (before) | F: Customer ID (after) |
|---|---|---|---|
| sarah@acmecorp.com | MKT-sarah@acmecorp.com | 78942 | CUST-78942 |
| jrivera@techflow.io | MKT-jrivera@techflow.io | 10233 | CUST-10233 |
| priya@nexgenlabs.co | MKT-priya@nexgenlabs.co | 55601 | CUST-55601 |
| mbell@veridion.ai | MKT-mbell@veridion.ai | 99204 | CUST-99204 |
Still with me? Now highlight E2:F11. Press Ctrl+C. Right-click column B → Paste Special → Values. Do the same for column C. Then delete columns E and F. Done. No trace of formulas. Just clean, static prefixed text.
The Result
This is what Marketing actually receives—no formulas, no blanks, no decimal creep. Every email starts with "MKT-", every ID with "CUST-". And because we used IF() + TEXT(), rows with missing data stayed empty instead of spitting out "MKT-" or "CUST-0".
| A: Full Name | B: Email | C: Customer ID | D: Last Contact |
|---|---|---|---|
| Sarah Chen | MKT-sarah@acmecorp.com | CUST-78942 | 2024-03-15 |
| James Rivera | MKT-jrivera@techflow.io | CUST-10233 | 2024-04-02 |
| Priya Mehta | MKT-priya@nexgenlabs.co | CUST-55601 | 2024-02-28 |
| Marcus Bell | MKT-mbell@veridion.ai | CUST-99204 | 2024-05-11 |
| Lena Kim | MKT-lena@stratosdev.com | CUST-33771 | 2024-01-22 |
| Diego Torres | MKT-dtorres@bluecore.net | CUST-88405 | 2024-03-30 |
| Anya Patel | MKT-anya@cloudspire.org | CUST-22199 | 2024-04-18 |
| Tariq Hassan | MKT-t.hassan@optivolt.co | CUST-66513 | 2024-02-14 |
| Nina Wu | MKT-nina.wu@quantumreach.net | CUST-44882 | 2024-05-05 |
| Eliot Frost | MKT-efrost@novarise.co | CUST-11327 | 2024-04-26 |
What Could Go Wrong
Three real mistakes I’ve seen derail this exact task in the last two weeks:
| Symptom | Cause | Fix |
|---|---|---|
| "CUST-78942.00" appears | Used ="CUST-"&C2 without TEXT() | Replace with ="CUST-"&TEXT(C2,"0") |
| #VALUE! in E2 | B2 contains a #N/A error from a broken VLOOKUP upstream | Wrap with IFERROR: =IFERROR(IF(B2="","", "MKT-"&B2), "") |
| Blank cells turn into "MKT-" | Formula was ="MKT-"&B2 (no IF check) | Always test for emptiness first: =IF(B2="","", "MKT-"&B2) |
