CONCATENATE vs CONCAT vs TEXTJOIN vs &

The short answer to CONCAT vs TEXTJOIN: use CONCAT, or the & sign, to join a few pieces of text, and use TEXTJOIN when every piece needs the same separator or some cells may be empty. CONCATENATE is the older name for CONCAT and still works. This guide is for anyone building full names, addresses, labels or IDs from separate columns.

CONCATENATE vs CONCAT vs TEXTJOIN vs & at a glance#

All four join text. They differ in whether they accept ranges, add separators and skip blanks.

"Excel 2019 and later" includes Excel 2021, Excel 2024, Microsoft 365 and Excel for the web.

How each one works#

& is an operator, like +. The other three are functions that take a list of pieces.

=text1 & text2 & ...
=CONCATENATE(text1, [text2], ...)
=CONCAT(text1, [text2], ...)
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

• & joins whatever is on each side. Add as many as you need.
• CONCATENATE joins up to 255 pieces, but each one must be a single cell or value.
• CONCAT works the same way but also accepts a range, such as C2:E2.
• TEXTJOIN takes a delimiter (the separator to put between pieces), then ignore_empty (TRUE to skip empty cells), then the pieces or ranges.

None of them adds spaces by itself. Any space, comma or dash you want must be typed in double quotes, like " " or ", ". The TEXTJOIN function guide covers its arguments in more detail.

The same text joined four ways#

Here is a small contact list. Felix has no street address.

Task 1: a full name#

All four return Nora Delgado:

=A2&" "&B2
=CONCATENATE(A2," ",B2)
=CONCAT(A2," ",B2)
=TEXTJOIN(" ",TRUE,A2:B2)

For two pieces, & is the shortest and works in every version. TEXTJOIN is overkill here, but it shows the idea: one separator, one range.

Task 2: an address from a range#

Now join the street, city and state with a comma and a space between them. CONCAT can take the range, but it adds nothing in between:

=CONCAT(C2:E2)
=TEXTJOIN(", ",TRUE,C2:E2)

CONCAT returns 418 Maple AvePortlandOR. TEXTJOIN returns 418 Maple Ave, Portland, OR. With &, you'd type every separator yourself: =C2&", "&D2&", "&E2.

Task 3: a row with an empty cell#

Felix's street is empty. Here is what each approach gives for row 3:

=C3&", "&D3&", "&E3
=TEXTJOIN(", ",FALSE,C3:E3)
=TEXTJOIN(", ",TRUE,C3:E3)

The first two return , Boston, MA, with a stray comma at the start. Only TEXTJOIN with TRUE returns the clean Boston, MA, because it skips the empty cell and its separator. This is the main reason to reach for TEXTJOIN.

Task 4: a list of names in one cell#

TEXTJOIN can also run down a column. This joins the first names into one line:

=TEXTJOIN(", ",TRUE,A2:A3)

The result is Nora, Felix. On a longer list, the same formula gives you a comma-separated list you can paste into an email. With &, you'd need one piece and one separator for every row.

When to use & or CONCAT#

Use & or CONCAT when you join a few specific pieces and want full control over what goes between them. They fit when:

• You join two or three cells, such as a first and last name.
• The separators differ, such as a product name followed by tags in brackets: =A2&" ("&B2&")".
• You join a range with no separator, such as code parts in C2:E2: =CONCAT(C2:E2).
• The file must work in any version. & works everywhere.
• You build a lookup key from two columns. =D2&"-"&E2 gives Portland-OR, a single value you can then look up with XLOOKUP or VLOOKUP when one column alone isn't unique.

Use CONCATENATE only when you're editing an older workbook that already uses it. It does nothing that & can't do.

When to use TEXTJOIN#

Use TEXTJOIN when every piece gets the same separator, or when some pieces may be blank. It fits when:

• You build addresses or lists, such as "418 Maple Ave, Portland, OR".
• Some cells are empty, such as optional tags or an address line 2.
• You join a long range, such as every tag in B2:E2: =TEXTJOIN(", ",TRUE,B2:E2).
• You want a list in one cell, such as all names in a column separated by commas.

The delimiter can also come from a cell. If H1 holds |, then =TEXTJOIN($H$1,TRUE,C2:E2) returns 418 Maple Ave | Portland | OR. Lock H1 with dollar signs so every row uses the same separator when you fill down, and change the separator for the whole column by editing one cell.

How to keep number and date formats when joining#

Joining turns numbers into plain text, and the cell's formatting is lost. If F2 holds 1250.5 formatted as $1,250.50:

="Total: "&F2
="Total: "&TEXT(F2,"$#,##0.00")

The first returns Total: 1250.5. The second returns Total: $1,250.50, because TEXT applies the number format before joining. Dates work the same way: a date joined directly shows its serial number, such as 46082, so use something like TEXT(B2,"mmm d, yyyy"). Percentages need it too: a rate of 0.256 in G2 joins as 0.256, while TEXT(G2,"0%") gives 26%.

Common mistakes when joining text#

These slips cause most messy results:

1. Forgetting the spaces. =A2&B2 gives NoraDelgado. Add " " between the pieces.
2. Using single quotes. Text in a formula needs double quotes: " ", not ' '.
3. Giving CONCATENATE a range. It expects single cells. Use CONCAT or TEXTJOIN for ranges.
4. Using FALSE in TEXTJOIN by accident. Empty cells still get a separator, so the result has , , in it. Use TRUE unless you need the gaps.
5. Losing number and date formats. Wrap numbers and dates in TEXT before joining.
6. Stray spaces in the source cells. "Nora " with a trailing space gives a double space. Clean the data with TRIM first.
7. Sharing CONCAT or TEXTJOIN with older Excel. Excel 2016 without Microsoft 365 shows #NAME?. Use & for files that must work everywhere.
8. Expecting the result to be a number. Joining always gives text, so =H1&H2 with 1 and 5 gives the text "15". SUM skips it in a range. Keep the maths in separate cells and join only for display.

A cell can hold at most 32,767 characters, so CONCAT and TEXTJOIN return #VALUE! if the joined result is longer than that. When you fill a joining formula down a column, check which references should stay fixed, such as a separator stored in one cell; see relative vs absolute references. For more ways to join text, see how to combine text in Excel.

Our recommendation#

Use & for quick joins of two or three pieces, because it is short and works in every version. Use TEXTJOIN whenever you need the same separator between several pieces or some cells may be empty: it is the only one that skips blanks cleanly. Use CONCAT when you want to join a range with no separator, and leave CONCATENATE to older files.

In short: & for a few pieces, TEXTJOIN for lists with separators or blanks, CONCAT for ranges, CONCATENATE only for compatibility.

If you also tidy numbers before you join them, our comparison of ROUND vs ROUNDUP vs ROUNDDOWN explains how to fix the decimals first.

Practise it#

Try the TEXTJOIN exercise on this page: Felix's empty street should disappear without leaving a comma behind. Then practise joining text with &, CONCAT and TEXTJOIN in a live spreadsheet, including product tags with gaps and a listing title built from several pieces.