The CONCAT function

Joins texts and ranges into one text. The & operator does the same for single cells.

How to use CONCAT

CONCAT glues pieces of text together into one cell, like taping strips of paper end to end. Each argument is a strip: a cell, a piece of text in quotes or a whole range. CONCAT adds nothing between the strips, so you put in the spaces and commas yourself.

The classic job is names. With first names in column A and last names in column B, =CONCAT(A2," ",B2) gives Anna Berg. The space is text, so it goes in quotes as its own argument. You can also build a product code from a prefix and a number, an email address from a name and a company domain, or a sentence like =CONCAT("Invoice ",A2," is due").

Unlike the & operator, CONCAT accepts a whole range. =CONCAT(A2:B2) joins every cell in it, but with nothing in between, so you get AnnaBerg. That suits a row of letters or digits and fits badly when every piece needs a space or comma. For that, use TEXTJOIN, which takes the separator once. For two or three cells, the & operator is just as short: =A2&" "&B2.

Numbers and dates come through as their raw values, so wrap them in TEXT when they need to look right.

Syntax

CONCAT(text1, [text2], ...)
text1
The first text, cell or range.
text2 (optional)
More texts or ranges.

Examples

D1=CONCAT(A2," ",B2)
ABCD
1FirstLastAnna Berg
2AnnaBerg

In plain words: Join the first name in A2, a space and the last name in B2.

D1=CONCAT(A2:B2)
ABCD
1FirstLastAnnaBerg
2AnnaBerg

Common CONCAT mistakes

  • The names run together, like AnnaBerg

    CONCAT puts nothing between the pieces. Add a space as its own argument: =CONCAT(A2," ",B2). For a whole range with a space between every cell, use =TEXTJOIN(" ",TRUE,A2:B2).

  • A date or percentage turns into a plain number

    CONCAT uses the raw value of the cell, and a date is a number underneath. Format it with TEXT: =CONCAT("Due: ",TEXT(A2,"mmm d, yyyy")), or TEXT(B2,"0%") for a percentage.

  • There is a double space when the middle name is empty

    The empty cell still gets its space. Wrap the result in TRIM, as in =TRIM(CONCAT(A2," ",B2," ",C2)), or use =TEXTJOIN(" ",TRUE,A2:C2), which skips empty cells.

  • The joined result looks like a number but will not add up or match

    CONCAT always returns text, even when every piece is a digit. Wrap it in VALUE when you need a real number: =VALUE(CONCAT(A2,B2)).

Read the guide

Questions

What is the difference between CONCAT and CONCATENATE?

CONCATENATE is the older function and cannot join a whole range. CONCAT can, and it arrived with Excel 2019. Very old versions of Excel have only CONCATENATE and the & operator.

Does CONCAT work in Google Sheets?

Only for two values. Google Sheets' CONCAT joins exactly two values and takes no ranges, so use CONCATENATE or TEXTJOIN there to join more.

Should I use CONCAT or TEXTJOIN?

Use CONCAT for a few pieces with no separator, and TEXTJOIN when you need the same separator between every piece or want to skip empty cells. =TEXTJOIN(", ",TRUE,A2:A6) lists a range with commas.

How do I put a line break between two joined values?

Join in CHAR(10), as in =CONCAT(A2,CHAR(10),B2), and turn on Wrap Text for the cell so the break shows.

Related functions