Most Excel tutorials tell you to use CONCATENATE or the ampersand (&) to append text. They’re wrong. CONCATENATE was deprecated in Excel 365 and Excel 2016. It doesn’t handle arrays, ignores empty cells by default, and breaks on #N/A without warning. You’re not just typing extra characters — you’re building fragile formulas.
The Problem
You have customer records where first names are in column A, last names in column B, and you need full names in column C. But your source data is messy: some rows have missing last names, others include middle initials, and a few contain leading spaces or line breaks. Worse — someone pasted values into column B using Paste Special → Values, breaking all existing formulas.
| A1: First Name | B1: Last Name | C1: Current Formula (Broken) | D1: Result |
|---|---|---|---|
| Sarah | Chen | =CONCATENATE(A2," ",B2) | Sarah Chen |
| James | =CONCATENATE(A3," ",B3) | James | |
| Miguel | Rodríguez | =A4&" "&B4 | Miguel Rodríguez |
| Lena | O'Connell | =A5&" "&B5 | Lena O'Connell |
| Tariq | Al-Mansoori | =CONCATENATE(A6," ",B6) | Tariq Al-Mansoori |
| Anya | =A7&" "&B7 | Anya | |
| Diego | de la Cruz | =CONCATENATE(A8," ",B8) | Diego de la Cruz |
See the pattern? Empty cells create trailing spaces. Missing values break readability. And if you try to add a title like "Mr. " before A2, CONCATENATE won’t skip it when A2 is blank — it just appends "Mr. " to nothing.
The Solution
Use TEXTJOIN. It’s built for this. It handles empties, skips blanks, accepts ranges, and works with arrays. No more nested IFs to check for blanks. Do this:
- In cell C2, type
=TEXTJOIN(" ",TRUE,A2:B2) - Press Enter. You’ll get "Sarah Chen" — clean, no trailing space.
- Select C2, then double-click the fill handle (bottom-right corner of the cell) to copy down to C8.
- Done. All rows now show properly spaced, blank-safe full names.
TEXTJOIN’s second argument — TRUE — tells Excel to ignore empty cells. That’s why C3 shows "James", not "James ". That one Boolean saves 7 nested IF statements.
| A1: First Name | B1: Last Name | C1: New Formula | D1: Result |
|---|---|---|---|
| Sarah | Chen | =TEXTJOIN(" ",TRUE,A2:B2) | Sarah Chen |
| James | =TEXTJOIN(" ",TRUE,A3:B3) | James | |
| Miguel | Rodríguez | =TEXTJOIN(" ",TRUE,A4:B4) | Miguel Rodríguez |
| Lena | O'Connell | =TEXTJOIN(" ",TRUE,A5:B5) | Lena O'Connell |
| Tariq | Al-Mansoori | =TEXTJOIN(" ",TRUE,A6:B6) | Tariq Al-Mansoori |
| Anya | =TEXTJOIN(" ",TRUE,A7:B7) | Anya | |
| Diego | de la Cruz | =TEXTJOIN(" ",TRUE,A8:B8) | Diego de la Cruz |
Going Further
You can append fixed text *before* or *after* dynamic content — but don’t hardcode spaces. Use TEXTJOIN’s delimiter control.
To prefix every name with "Client: ", use:=TEXTJOIN("",TRUE,"Client: ",A2," ",B2)
Need to append a date in YYYY-MM-DD format? Don’t convert manually. Use:=TEXTJOIN(" - ",TRUE,A2,B2,TEXT(TODAY(),"yyyy-mm-dd"))
Appending from non-adjacent columns? TEXTJOIN accepts multiple ranges:=TEXTJOIN(", ",TRUE,A2,C2,E2) — pulls from A2, C2, and E2, skipping any blanks.
Surprising tip: TEXTJOIN works inside FILTER. If you filter a list of contacts and want each result appended with "(Active)", wrap it like this:=TEXTJOIN(CHAR(10),TRUE,FILTER(A2:A20&" (Active)",B2:B20="Yes"))
This returns a single cell with line breaks between each active client — no helper columns needed.
When NOT to Use This
Don’t use TEXTJOIN if you’re running Excel 2013 or earlier. It doesn’t exist there. Use this fallback instead:=TRIM(A2&" "&B2&" "&C2) — TRIM removes extra spaces, including leading/trailing ones.
Avoid TEXTJOIN for large datasets (>100k rows) where performance matters. Each TEXTJOIN recalculates the entire range. For static reports, fine. For live dashboards updating every 5 seconds? Pre-process in Power Query.
Never nest TEXTJOIN inside array formulas that spill across thousands of rows unless you’ve tested memory usage. It’s fast — but not magic.
If your appended text must preserve line breaks from source cells (like addresses), TEXTJOIN’s delimiter won’t help. Use SUBSTITUTE + CHAR(10) instead — but that’s a separate workflow.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Insert function dialog | Shift + F3 | Type "TEXTJOIN" and press Tab to select |
| Edit formula in cell | F2 | Then arrow keys to navigate inside the formula |
| Toggle absolute/relative refs | F4 | After selecting A2 in formula bar, press F4 to lock as $A$2 |
| Open Excel Options | Alt + F + T | Check version under 'About Excel' if unsure about TEXTJOIN support |
