FormuladrillCONCAT and &
CONCAT(text1, [text2], ...) joins text, numbers and ranges into one string with no separator; the & operator does the same and is quicker to type: =A2 & " " & B2. CONCATENATE is the older function that cannot take ranges. For a separator between every item use TEXTJOIN.
Syntax
CONCAT(text1, [text2], ...) or text1 & text2 & ...| Part | What it is |
|---|---|
text1, text2... | Cells, strings in quotes, numbers or ranges to join in order. |
Examples
=CONCAT(A2, " ", B2)Result: Jane Doe when A2 is Jane and B2 is Doe
=LOWER(A2 & "." & B2 & "@example.com")Result: jane.doe@example.com
The & operator does the same as CONCAT.
=CONCAT("ORD-", TEXT(B2, "0000"))Result: ORD-0042 when B2 is 42
TEXT keeps the leading zeros.
="Paid on " & TEXT(C2, "d mmm yyyy")Result: Paid on 3 Nov 2026 when C2 is that date
Without TEXT the date joins as its serial number.
Common mistakes
- Names run together (JaneDoe): no space was added. Include " " between the parts.
- A date joins as a number like 46329: wrap it in TEXT with a date format.
- #NAME? for CONCAT in Excel 2016 or earlier: use CONCATENATE or &.
- CONCATENATE(A2:A10) returns only A2 or an error; CONCAT accepts ranges, CONCATENATE does not.
Practice CONCAT and &
These drills check your answer on live data, free, no account needed.
- Build an email addressCreate each email address from the names: the first letter of the first name, then the last name, all lowercase, then @acme.com. For example Maria Lopez becomes mlopez@acme.com. Fill C2 and C3.
Questions
CONCAT or CONCATENATE?
CONCAT is the current function and accepts ranges. CONCATENATE still works for compatibility. Most people just use & for small joins and TEXTJOIN for lists.
How do I join cells with a comma between them?
=TEXTJOIN(", ", TRUE, A2:A10). With & you would have to write the comma between every pair.
How do I add a line break when joining?
Use CHAR(10): =A2 & CHAR(10) & B2, then turn on Wrap Text for the cell.
Related
- TEXTJOINHow the Excel TEXTJOIN function joins a range into one string with a separator and skips empty cells, with examples for comma lists and a conditional join.
- TEXTHow the Excel TEXT function turns a number or date into text in a format you choose, with examples for dates, thousand separators, percentages and padding.
- LEN, LEFT, RIGHT, MIDHow to pull characters from the start, end or middle of a cell with LEFT, RIGHT and MID, measure length with LEN, and use FIND to split text at a character.
- PROPER, UPPER, LOWERHow to change the case of text in Excel with PROPER, UPPER and LOWER, with examples for cleaning names and emails, TRIM, and the names PROPER gets wrong.