CONCAT vs TEXTJOIN vs &: which should you use?

The & operator: quick and works everywhere

The ampersand joins whatever is on either side of it. It works in every version of Excel, Google Sheets and LibreOffice, and it is the easiest to read for a short join. You type separators yourself in quotes.

Pick & for two to four pieces. Beyond that, the chain of &" "& gets hard to read and easy to break.

CONCATENATE and CONCAT: the same idea as a function

CONCATENATE(A3," ",B3," ",C3) does exactly what & does, as a function. It works in all versions but only takes single cells: CONCATENATE(A3:C3) does not join the range in a normal formula.

CONCAT replaced it in Excel 2019. It accepts ranges, so =CONCAT(D2:E2) joins a whole row. But it has no separator and no way to skip blanks, so it is mostly useful for joining codes or parts that need nothing in between.

TEXTJOIN: separators and blank-skipping built in

TEXTJOIN(delimiter, ignore_empty, text1, ...) puts the delimiter between every item, and with TRUE as the second argument it skips empty cells. That second part is what makes it the right tool for names with an optional middle name, or addresses with an optional second line.

Maria has no middle name. =A2&" "&B2&" "&C2 gives "Maria Lopez" with two spaces. TEXTJOIN gives a clean single space.

Join a whole column into one cell

This is where TEXTJOIN has no real rival. Joining a list into one comma-separated cell with & would mean clicking every cell. TEXTJOIN does it with one range, which is handy for email lists or a summary line.

In Excel 365 you can join only the rows that match a condition by feeding it FILTER: =TEXTJOIN(", ",TRUE,FILTER(A2:A4,F2:F4>0)) lists the customers who owe money.

Numbers and dates lose their format

All four methods join the underlying value, not what the cell shows. A balance of 1,250.50 formatted as currency joins as 1250.5, and a date joins as its serial number. Wrap the value in TEXT with the format you want.