When working on Excel spreadsheets, assembling text from multiple cells is a common operation. Historically, the CONCATENATE function was long used for this task, before Excel introduced the more modern CONCAT function. The goal of this article is to clarify the major differences between these two functions, to show in which cases it is better to use one or the other, and to provide practical examples to master their syntax.
1. Origin and compatibility
1.1. CONCATENATE: a “classic” function
The CONCATENATE function has been available in Excel since very old versions (Excel 2007 and earlier). It allows chaining multiple text strings or cell references. However, this function is gradually considered “legacy” and no longer receives major updates.
1.2. CONCAT: a modernized version
Introduced from Excel 2016 and natively integrated into Office 365, the CONCAT function aims to be a more flexible replacement. It retains everything that already works with CONCATENATE, while simplifying certain uses and supporting entire ranges without manual listing.
2. Syntax and basic usage
2.1. CONCATENATE syntax
CONCATENATE requires listing each argument separately, which can become tedious if dealing with many cells:
=CONCATENATE(text1; text2; text3; …)
Example:
=CONCATENATE(A1; ” “; B1; ” – “; C1)
assembles the content of cells A1, B1, and C1 by inserting a space and a dash.
2.2. CONCAT syntax
With CONCAT, the formula is more flexible. You can pass entire ranges as arguments and mix references, constants, and formula results:
=CONCAT(range1; range2; …)
Example:
=CONCAT(A1:C1; ” – Total”)
takes all the content from the range A1:C1 then adds the text ” – Total”.
3. Strengths and limitations
| Characteristic | CONCATENATE | CONCAT |
|---|---|---|
| Compatibility | Excel 2007 to Office 365 | Excel 2016 and later versions |
| Arguments | List of elements (limit 255) | Allowed ranges + list of elements |
| Handling empty cells | Ignores empty strings | Ignores empty cells in ranges |
| Readability | Long formula as soon as many elements | More compact thanks to ranges |
| Official replacement | Discouraged in the long term | Recommended by Microsoft |
4. Practical examples
4.1. Concatenating first and last names
To display “FirstName LastName” from two columns, you can use either function:
- =CONCATENATE(B2; ” “; A2)
- =CONCAT(B2; ” “; A2)
The result is identical, but the CONCAT version remains more scalable if, for example, you add an additional column.
4.2. Assembling a range of values
Imagine you have a series of product codes in A1:A10 and you want to assemble them into a single string. With CONCATENATE, you would have to write:
=CONCATENATE(A1; A2; A3; …; A10)
However, with CONCAT:
=CONCAT(A1:A10)
It is shorter, more readable, and faster to adapt if the range changes.
4.3. Integration into more complex formulas
When you combine text with numeric results or dates, CONCAT works the same way, preserving the native Excel format. To force a specific format (e.g. date dd/mm/yyyy), you need to wrap the result in the TEXT function:
=CONCAT(“Delivery date: “; TEXT(D2; “dd/mm/yyyy”))
5. Best practices
- Prefer CONCAT in recent versions of Excel for its flexibility.
- Use CONCATENATE only if compatibility with older versions is critical.
- Stay consistent: do not alternate between the two within the same workbook.
- Think about readability: comment or name your ranges if you assemble many elements.
6. To go further
Excel offers other complementary text functions: LEFT, RIGHT, MID, or FIND to extract and locate substrings. If you want to explore these text manipulation functions in depth, you can consult a dedicated guide to text functions in Excel.
Conclusion
In summary, CONCATENATE and CONCAT serve the same purpose: assembling text or references. But CONCAT, more modern, offers a streamlined syntax and support for ranges, making it particularly suitable for recent versions of Excel. In the long run, it is wise to migrate your CONCATENATE formulas to CONCAT to benefit from better maintenance and future compatibility.
FAQ
- Which function for Excel 2010? Use CONCATENATE, CONCAT does not exist in this version.
- Can numbers and text be mixed? Yes, but if you want to control the format, wrap the number with the TEXT function.
- Is there a limit on arguments? CONCATENATE is limited to 255 arguments; CONCAT does not have this strict limit thanks to ranges.
- How to handle empty cells? Both functions ignore empty cells, which avoids superfluous separators.
