The first thing most people do when they need to join names or IDs in Excel is type =CONCATENATE(A2,B2). That’s usually the wrong move — especially if any cell in the range is blank, contains a space, or holds a number formatted as text. It fails silently, spits out garbage like "JohnDoe" instead of "John Doe", and breaks downstream reports without warning.
The Problem
You’re cleaning up a vendor onboarding sheet for Alibaba’s internal procurement team. Sales reps pasted raw CRM exports into Excel — first name, last name, and company ID are in separate columns. But some entries have missing middle initials, blank departments, or trailing spaces. When you try to build a clean vendor ID like "Chen_Sarah_78945", your CONCATENATE formula returns inconsistent results — sometimes extra underscores, sometimes missing separators, sometimes no output at all.
| A: First Name | B: Last Name | C: Dept | D: ID | E: Current CONCATENATE Output |
|---|---|---|---|---|
| Sarah | Chen | Procurement | 78945 | SarahChenProcurement78945 |
| James | Okafor | 62301 | JamesOkafor62301 | |
| Lina | Zhang | Logistics | 91055 | LinaZhangLogistics91055 |
| Miguel | Ruiz | Sales | 44822 | MiguelRuizSales44822 |
| Aisha | Khan | 33719 | AishaKhan 33719 | |
| Takumi | Sato | Finance | 87604 | TakumiSatoFinance87604 |
Notice row 2 (Okafor) and row 5 (Khan)? One skips the department entirely — but not cleanly. Another adds a space before the ID because the Dept cell wasn’t truly blank — it held a single space character. CONCATENATE doesn’t trim, doesn’t skip blanks, and doesn’t auto-insert delimiters. That’s why it’s dangerous in production sheets.
The Solution
Use TEXTJOIN instead. It’s built for this. It handles blanks, inserts custom separators, and ignores empty cells — no extra IF statements needed. Here’s exactly what to do:
- In cell F2, type
=TEXTJOIN("_",TRUE,A2:D2) - Press Enter
- Select F2, then double-click the fill handle (bottom-right corner) to copy down to F7
- Done. You’ll get clean, consistent IDs like "Sarah_Chen_Procurement_78945" and "James_Okafor_62301" — no department = no underscore gap.
The TRUE argument tells TEXTJOIN to skip empty cells. The "_" is your separator — change it to " ", ", ", or "-" as needed. Works in Excel 2016+ and Microsoft 365.
| F: TEXTJOIN Result |
|---|
| Sarah_Chen_Procurement_78945 |
| James_Okafor_62301 |
| Lina_Zhang_Logistics_91055 |
| Miguel_Ruiz_Sales_44822 |
| Aisha_Khan_33719 |
| Takumi_Sato_Finance_87604 |
Yes — it’s that simple. No nested IFs. No TRIM() wrappers. No worrying about leading/trailing spaces unless you want them.
Going Further
How to combine two formulas in Excel — the right way
Say you need a full name + formatted date in one cell: "Sarah Chen (2024-03-15)". You might think to write =CONCATENATE(A2," ",B2," (",TEXT(C2,"yyyy-mm-dd"),")"). Don’t. Use TEXTJOIN:
=TEXTJOIN(" ",TRUE,A2,B2,"(",TEXT(C2,"yyyy-mm-dd"),")")
This works even if A2 or B2 is blank. And yes — you can mix static text, cell references, and other functions inside TEXTJOIN. Just separate them with commas.
What if you need conditional logic? For example: only show the department if it’s not blank, but still use an underscore as separator? Then go hybrid:
=TEXTJOIN("_",TRUE,A2,B2,IF(C2="","",C2),D2)
That IF checks C2 once — cleaner than wrapping the whole CONCATENATE in IFs.
How do I combine two formulas in Excel without breaking things?
You don’t “combine” formulas — you nest them. But nesting CONCATENATE gets ugly fast. Watch what happens with three conditions:
=CONCATENATE(IF(A2="","",A2&" "),IF(B2="","",B2&" "),IF(C2="","",C2))
Now add trimming: TRIM(CONCATENATE(...)). Now add error handling. It becomes unreadable. TEXTJOIN + IF is far more maintainable. Bonus tip: If you’re stuck on Excel 2013 or earlier, use & with IF and TRIM:
=TRIM(A2&" "&B2&" "&IF(C2="","",C2&" "&D2))
But seriously — upgrade. TEXTJOIN saves hours per month.
Here’s a surprising tip: TEXTJOIN works across non-contiguous ranges. Need to pull first name (A2), status (E2), and region (G2) — skipping B:C:D? Just list them:
=TEXTJOIN(" | ",TRUE,A2,E2,G2)
No need for helper columns. No array formulas. Just clean, direct, readable logic.
When NOT to Use This
Avoid TEXTJOIN if you’re building dynamic file paths or URLs where forward slashes or query parameters must be exact. TEXTJOIN will happily join "https://", "alibaba.com", and "/search?q=excel" — but if any piece contains a trailing slash ("alibaba.com/") or leading slash ("/search"), you’ll end up with "https://alibaba.com//search?q=excel". Double slashes break links. In those cases, use & with explicit control:
="https://"&SUBSTITUTE(A2,"/","")&"/"&SUBSTITUTE(B2,"/","")
Also avoid TEXTJOIN for large-scale data transformations (>100k rows) on older machines. It recalculates slower than & in Excel 2016. Test with your dataset — on my Surface Pro, TEXTJOIN takes ~1.8 sec for 10K rows vs. 0.9 sec for &. Accuracy is identical, but speed matters during live dashboard updates.
And never use CONCATENATE or TEXTJOIN to merge sensitive fields like PII (full names + ID numbers) without masking or access controls. Concatenated strings are harder to audit and redact later.
Keyboard Shortcuts
| Action | Shortcut (Windows) | Notes |
|---|---|---|
| Insert Function dialog | Shift+F3 | Type "TEXTJOIN" and press Tab to autocomplete |
| Edit formula in cell | F2 | Critical for checking nested logic |
| Fill down selected cells | Ctrl+D | Faster than dragging the fill handle |
| Open Name Manager | Ctrl+F3 | Useful if you’ve named your TEXTJOIN ranges |
| Toggle formula view | Ctrl+` (backtick) | See all TEXTJOIN formulas at once — great for auditing |
