CONCAT
Joins text from several cells into one: a first name with a last name, a city with a street, a code with a number.
CONCAT(text1, [text2], ...)- [ ]optional part
- text1
- The first piece: a cell, a range or text in quotes.
- text2
- The next piece. There can be many such arguments, separated by commas.Optional
Return value
Text: all the arguments joined together with no separator.
Examples
First and last name in one cell
fx
A
B
C
D
1
2
3
4
5
6
7
First name
Last name
Employee
John
Smith
Emily
Davis
Mike
Brown
Sarah
Johnson
Sheet1
Details
CONCAT joins its arguments in order and adds nothing between them. You add a space or a comma yourself as a separate argument in quotes: =CONCAT(A2," ",B2).
The & sign does the same thing: =A2&" "&B2. CONCAT is handier when there are many pieces or you need to join a whole range in a row, for example A2:C2.
The result is always text, even if you joined numbers. The cell format is lost: a number that shows as $1,290 on the sheet becomes 1290 in the joined text.
Common mistakes
- The separator is missing: you get "JohnSmith". Add the space as a separate argument " ".
- Text in the formula without quotes: Excel looks for a function or name with that spelling and returns #NAME?.
- A joined number has become text, and SUM skips such cells.
In Google Sheets
In Google Sheets, CONCAT joins only two values; for many pieces and ranges, use CONCATENATE.