Arguments
text1- The first text or cell to join.text2(optional) - Additional text or cells.
CONCAT & the & operator
Joins text from several cells or literal strings into one value.
Syntax
=A1 & " " & B1 or =CONCAT(text1, [text2], …)=A2 & " " & B2Returns: First and last name with a space
The & operator is the most portable way to join text.
=CONCAT(A2:A5)Returns: All values in the range run together
CONCAT accepts ranges; use TEXTJOIN when you need a delimiter.
How CONCAT & the & operator evaluates
There are four ways to join text in Excel and they are not interchangeable. The & operator is the oldest and the most portable: =A2 & " " & B2 works in every version, in Google Sheets and in LibreOffice, and it is the only one of the four that is also an operator, so it can appear anywhere an expression can. CONCATENATE does the same job as a function and takes up to 255 arguments, but it refuses ranges - =CONCATENATE(A2:A5) joins nothing useful, because each argument must be a single value. Microsoft now describes CONCATENATE as kept for backward compatibility only.
CONCAT, added in Excel 2019, is the replacement: it accepts ranges, so =CONCAT(A2:A5) runs the four values together in one argument. What it does not do is put anything between them. If you want separators, TEXTJOIN is the function you actually want - CONCAT is for cases where the values are meant to butt up against each other, such as rebuilding a code from its parts. A multi-column range passed to CONCAT is read left to right across each row, then down.
All four share one behaviour that surprises people: joining reads the value in the cell, not what the cell displays. Number formats are not carried across. A cell showing £1,250.00 joins as 1250, a cell showing 15/03/2024 joins as its date serial 45366, and 15% joins as 0.15. The fix is TEXT(): ="Due " & TEXT(A2, "d mmm yyyy"). The result of any join is text, so a joined number cannot be summed until VALUE converts it back.
- Ranges
- CONCAT and TEXTJOIN accept whole ranges as one argument. CONCATENATE and the & operator do not - each needs its values one at a time.
- Blank cells
- Contribute nothing at all, in every one of the four methods. =A2 & B2 with B2 empty returns just A2, and neither a space nor a zero is inserted.
- Numbers and dates
- Joined as their stored value with the number format discarded. Dates and times become serial numbers. Wrap them in TEXT to control the appearance.
- Booleans
- TRUE and FALSE join as the words TRUE and FALSE, in capitals, regardless of how the cell displays them.
- Errors
- Any error among the joined values becomes the result. =A2 & B2 where B2 is #N/A returns #N/A, not the text "#N/A".
- Result type and length
- Always text, capped at a cell's 32,767 characters. Concatenating a number produces text that will not sum until it is passed through VALUE.
Errors CONCAT & the & operator returns
| Error | What it means and how to fix it |
|---|---|
| #NAME? | CONCAT used in Excel 2016 or earlier, which does not have it - it appears as _xlfn.CONCAT in a file made elsewhere. Also caused by unquoted literal text: =A2 & Ltd instead of =A2 & "Ltd". Use CONCATENATE or & for older versions. |
| #VALUE! | The joined result exceeds 32,767 characters, or a range was passed to CONCATENATE, which expects single values. Switch to CONCAT or TEXTJOIN for ranges. |
| #N/A or another propagated error | One of the joined cells already holds that error, usually from a lookup. Wrap the source in IFERROR before joining: =IFERROR(VLOOKUP(...), "") & B2. |
| #REF! | A cell in the expression was deleted. Rebuild the reference; joins over deleted cells cannot be recovered by editing the text. |
More examples
=A2 & ", " & B2 & " " & C2Returns: Smith, John A
The portable pattern. Every separator is typed out, so blank middle values leave a doubled separator behind - guard with TRIM or use TEXTJOIN if that matters.
=CONCAT(A2:C2)Returns: The three cells of a row run together
Excel 2019 and later only. Useful for rebuilding a part number from its columns, where no separator is wanted.
="Invoice " & TEXT(A2, "0000") & " due " & TEXT(B2, "d mmm yyyy")Returns: Invoice 0042 due 15 Mar 2024
TEXT restores the padding and the date format that a bare join throws away. The result is text, so it will sort alphabetically, not by date.
Compatibility
| Application | Support |
|---|---|
| Excel 2007-2016 | & and CONCATENATE only. CONCAT returns #NAME?. |
| Excel 2019 | All three available. CONCATENATE is retained for compatibility but no longer recommended by Microsoft. |
| Excel 2021 / Microsoft 365 | All three, plus TEXTJOIN and the array functions. =CONCAT(FILTER(...)) works without special entry. |
| Google Sheets | &, CONCATENATE and CONCAT all present, but Sheets' CONCAT takes exactly two arguments - it is not the range-joining version Excel 2019 introduced. |
| LibreOffice Calc | &, CONCATENATE and CONCAT are all supported, with CONCAT accepting ranges as it does in Excel 2019. |
Does CONCAT & the & operator Work in Google Sheets?
Yes. CONCAT & the & operator uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.
In the ExcelTool editor
The ExcelTool editor implements CONCAT, CONCATENATE and the & operator, and keeps the distinction between them: CONCAT flattens every argument so a range joins cell by cell, while CONCATENATE reduces each argument to a single value and returns #VALUE! if a multi-cell range is passed to it.
Joining follows Excel's text conversion rules - a blank contributes an empty string, TRUE and FALSE become the words TRUE and FALSE, and a number is written out from its stored value, so a date joins as its serial number rather than as a formatted date.
An error value anywhere among the joined values is returned as the result of the whole expression rather than being rendered as text.
The editor's & operator broadcasts across ranges: =A1:A3 & "-x" produces a three-cell array rather than a single value, which then spills into the cells below the formula.
You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.
CONCAT & the & operator questions
Use &. CONCAT does not exist before Excel 2019, and a workbook saved with it opens in 2016 showing _xlfn.CONCAT and a #NAME? error in every cell that used it. The & operator has worked unchanged since the earliest versions and behaves identically in Google Sheets and LibreOffice, so it is the safe choice for anything shared.
The zero was never in the value - it came from a number format such as 00000. Joining reads the stored number, which is 1234 rather than 01234. Rebuild it with TEXT(A2, "00000") inside the join, or store the column as text in the first place so nothing is lost on import.
No. CONCATENATE expects one value per argument and cannot expand a range; in older versions it returns #VALUE! or, in an array-aware version, only the value from the intersecting row. Use CONCAT for a range without separators or TEXTJOIN when you need commas between the values.
A join produces text, so a joined key like "AB" & 12 gives "AB12", which will not match a numeric 12 or a value with different spacing. Check with =EXACT(D2, F2), which is case-sensitive and unforgiving, and make sure both sides are built by the same formula. If the joined value is meant to be numeric, wrap the result in VALUE.