CONCATENATE in Excel: join text from two cells

Joining text — first name plus last name, city plus country — is one of the most common jobs in Excel. There are three ways, and the oldest is the longest.

The old way: CONCATENATE

=CONCATENATE(A1, " ", B1)

Joins A1, a space, and B1. It still works in every version, but Excel now hides it from the suggestions.

The short way: &

=A1 & " " & B1

Same result, less typing. The " " is a space in quotes — without it you get AnaRoy.

Example

Add a comma, a dash, anything

=B1 & ", " & A1

Gives Roy, Ana. Whatever sits inside the quotes is inserted as-is.

Join a whole column: TEXTJOIN

=TEXTJOIN(", ", TRUE, A1:A20)

Joins every cell in A1:A20 with a comma and space. TRUE skips blanks. Excel 2019 and newer.

Numbers and dates come out wrong?

A date joined with & shows as a number like 45231. Wrap it in TEXT: =A1 & " on " & TEXT(B1, "dd mmm yyyy").

Turn the formulas into plain text

Copy the results, then right-click › Paste Special › Values. Now you can delete the original columns.