Joining Text in Excel: CONCAT, TEXTJOIN and the & Operator
Sticks several values together into one, with or without a separator.
Joining text is one of the first things anybody needs and one of the messiest corners of Excel, because there are four ways to do it and three of them are nearly the same. The ampersand operator, &, is the oldest and still the best for short joins: =A2&" "&B2 puts a space between two cells and reads almost like a sentence.
CONCAT replaced the older CONCATENATE and accepts ranges, so =CONCAT(A2:C2) joins three cells without naming each one. What it will not do is put anything between them, which is why the results run together.
TEXTJOIN is the one worth learning properly. It takes a delimiter as its first argument and puts it between every piece, and its second argument decides whether empty cells are skipped — which is the difference between "Alice, , Moreau" and "Alice, Moreau" when a middle field is blank.
Syntax
=A2&" "&B2 =CONCAT(text1, [text2], …) =TEXTJOIN(delimiter, ignore_empty, text1, …)Arguments
delimiterRequired- TEXTJOIN only: what to put between each piece. Use ", " for a list, " " for a sentence, CHAR(10) for line breaks.
ignore_emptyRequired- TEXTJOIN only: TRUE skips blank cells, FALSE leaves a delimiter where each blank was. TRUE is nearly always what you want.
text1, text2, …Required- The values to join. CONCAT and TEXTJOIN accept whole ranges; the & operator takes one value at a time.
The example data
Headers in row 1, data in A2:C5. Note the stray spaces in A3 and A5.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Code | |
| 2 | Alice Moreau | alice@northwind.com | NW-2024-001 |
| 3 | bruno santos | bruno@southgate.co.uk | SG-2024-014 |
| 4 | CHEN WEI | chen@northwind.com | NW-2023-207 |
| 5 | Dana Okafor | dana@eastvale.org | EV-2024-092 |
Worked examples
=A2&" ("&C2&")"Result: Alice Moreau (NW-2024-001)
The & operator with literal text between the cells. Quotes around anything you type.
=CONCAT(A2, C2)Result: Alice MoreauNW-2024-001
CONCAT joins with nothing between, which is exactly the problem it does not solve.
=TEXTJOIN(", ", TRUE, A2, B2, C2)Result: Alice Moreau, alice@northwind.com, NW-2024-001
One delimiter, applied between every piece. No repetition in the formula.
=TEXTJOIN(", ", TRUE, A2:A5)Result: Alice Moreau, bruno santos , CHEN WEI, Dana Okafor
A whole column into one cell. The stray spaces come through, which is why TRIM usually belongs in here somewhere.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#VALUE!
Why it happens: One of the joined cells contains an error, which propagates through the join.
How to fix it: Wrap the offending reference in IFERROR before joining it.
Numbers lose their formatting
Why it happens: Joining converts to text using the underlying value, so a date becomes 45678 and a currency loses its symbol.
How to fix it: Format explicitly with TEXT: =A2&" — "&TEXT(D2, "£#,##0.00").
Everything runs together
Why it happens: CONCAT and CONCATENATE insert nothing between values.
How to fix it: Add the separator yourself with &, or use TEXTJOIN, which is built for this.
#NAME?
Why it happens: CONCAT and TEXTJOIN need Excel 2019 or later.
How to fix it: Older versions have CONCATENATE and the & operator, both of which still work everywhere.
Tips worth knowing
- & is clearer than CONCAT for two or three values; TEXTJOIN wins the moment there is a delimiter or a range.
- CHAR(10) as the delimiter gives line breaks inside one cell — turn on Wrap Text or you will not see them.
- TEXTJOIN with ignore_empty set to TRUE is the clean way to build an address from fields that are sometimes blank.
- Wrap the inputs in TRIM when joining imported data, or the padding ends up in the middle of your sentence.
Frequently asked questions
What is the difference between CONCAT and CONCATENATE?
CONCAT replaced CONCATENATE in Excel 2019 and adds one thing: it accepts ranges, so CONCAT(A2:C2) works where CONCATENATE needed each cell named. CONCATENATE still functions for backwards compatibility but Microsoft lists it as retired.
How do I add a space between joined values?
Include it as a literal: =A2&" "&B2. The quotes matter — they are what tells Excel the space is text you are supplying rather than part of a reference. With TEXTJOIN you pass " " as the delimiter once instead.
Why does my joined date turn into a number?
Excel stores dates as serial numbers and joining exposes the underlying value. Wrap it in TEXT with a format string: =A2&" — "&TEXT(B2, "dd/mm/yyyy").
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.