Function guides ยท Text
CONCAT function
Joins text from several cells or ranges into one string.
Syntax
=CONCAT(text1, [text2], ...)
| Argument | What it is |
|---|---|
text1 | Text, a cell reference or a range to join. |
text2, ... | Optional. More text items or ranges. |
What CONCAT does
CONCAT joins pieces of text into one cell. The pieces can be quoted text, cell references or whole ranges, and they run together with nothing between them.
It succeeds CONCATENATE, which could not accept a range. The result is the same, but the formula is shorter and handles a block of cells in one go.
The behavior to remember is that there is no separator. Spaces, commas and dashes must be supplied as their own arguments, in quotes. When the join uses the same separator everywhere, TEXTJOIN does that for you.
Three ways to join text exist, and they overlap. The ampersand is the shortest for a few pieces: =A2&" "&B2. CONCATENATE does the same with commas and is retained for old workbooks. CONCAT is the modern version that also reads ranges. All three return plain text, and none of them formats a number, so dates and percentages need TEXT around them.
Joined text is a formula result. If you need the text to stay when the source cells change or are deleted, copy the column and paste it back as values.
CONCAT examples
1. First and last name
| A | B | C | |
|---|---|---|---|
| 1 | First | Last | Full name |
| 2 | Mei | Tanaka | Mei Tanaka |
CONCAT joins its arguments with nothing between them, so the space is added as its own argument, written as " ". Without it the names run together.
2. Build a product code
| A | B | C | |
|---|---|---|---|
| 1 | Dept | Number | |
| 2 | H | 932 | H-0932 |
Text, cell values and calculated pieces can be mixed. This makes a code from a department letter, a dash, and a number padded to four digits with TEXT.
3. Join a whole range
| A | B | |
|---|---|---|
| 1 | A | ABCDE |
| 2 | B | |
| 3 | C | |
| 4 | D | |
| 5 | E |
Unlike the old CONCATENATE, CONCAT accepts a range. All five cells are joined with nothing between them. When a separator is needed, TEXTJOIN is the better tool.
Common CONCAT mistakes
- No spaces. CONCAT adds no separator.
=CONCAT(A2,B2)gives MeiTanaka. Add " " between. - Numbers lose their formatting. A date or a percentage joins as its underlying number. Wrap it in TEXT:
TEXT(B2,"mm/dd/yyyy"). - Using it in Excel 2016. CONCAT arrived in Excel 2019. Older versions need CONCATENATE or the ampersand.
- Joining a range with blanks. Blank cells add nothing but also no separators, so gaps are invisible. TEXTJOIN can skip them cleanly.
CONCAT on Excel job tests
"Combine first and last names", "build the employee email address" and "create the order reference from region and number" can all be done with the ampersand, CONCATENATE, CONCAT or TEXTJOIN. The result is what counts.
The formatting is where people trip. A date joined into text turns into a serial number such as 46095 unless TEXT formats it, and a number loses its leading zeros unless TEXT pads it.
"Separated by commas, skipping blanks" describes TEXTJOIN.
Related functions
CONCAT questions
- What is the difference between CONCAT and CONCATENATE?
- CONCAT replaces CONCATENATE and accepts ranges. CONCATENATE needs each cell listed and is kept for compatibility.
- How do I add a space between joined cells?
- Add " " as its own argument:
=CONCAT(A2," ",B2). - What is quicker, CONCAT or the ampersand?
- For two or three items,
=A2&" "&B2is shorter. CONCAT is better when you want to join a range. - How do I keep a date as a date when joining?
- Wrap it in TEXT with a format code, for example TEXT(A2,"mm/dd/yyyy").
Practice CONCAT
The lesson Joining text: &, CONCAT and TEXTJOIN (module 9 of Everyday Excel) has checked CONCAT exercises. It is part of Full Access; only the first two lessons of each module are free. The free 5-item sample test uses the same kind of formulas in a working grid if you want to try before you decide.
Try a 5-item Excel test sample