Between older versions and recent editions of Excel, you may have noticed two almost twin functions: CONCATENATE and CONCAT. Behind this similarity in name lie differences that affect ease of use, compatibility, and maintainability of your files. Let’s explore together how to choose the function best suited to your needs.
🔍 CONCATENATE is Excel’s historic formula, designed to link up to 255 distinct values, but considered cumbersome when multiplying arguments.
⚡ CONCAT arrives with Office 365 and Excel 2019, lightening the syntax and accepting dynamic ranges, simplifying the assembly of series of cells without enumerating each argument.
📊 In terms of performance, CONCAT is gaining ground: it easily handles long data ranges and integrates better with nested formulas.
🚀 For more advanced needs, favor TEXTJOIN or a Power Query approach if you want to massively merge sources or process data linked via connection to a SQL database.
Somaire
Origin and context of appearance
CONCATENATE existed long before Excel’s dynamic functions. Its learning curve is simple, but its writing can quickly become verbose when chaining about twenty cells. With the advent of Excel 2019 and Office 365, Microsoft introduced CONCAT, a way to modernize formula language and open the door to input ranges.
Syntax and detailed arguments
CONCATENATE function
The syntax is as follows:
=CONCATENATE(text1; text2; … text_n)
You can list up to 255 arguments, whether they are isolated cells or literal strings. Each fragment becomes a piece added to the final result. However, to introduce a space or a separator character, you must manually add a dedicated argument:
=CONCATENATE(A1; " "; B1)
Needless to say, multiplying separators quickly makes the formula expand.
CONCAT function
Introduced more recently, its form is more concise:
=CONCAT(range_or_text1; range_or_text2; …)
You can pass an entire range, for example =CONCAT(A1:A5), which chains A1 to A5 without having to enumerate each cell. Be careful though: unlike TEXTJOIN, CONCAT does not handle a native separator, it simply concatenates the values in sequence.
Practical cases and illustrations
To better visualize these differences, let’s take a small set of first names and last names:
| First name | Last name | CONCATENATE formula | CONCAT formula |
|---|---|---|---|
| Alice | Durand | =CONCATENER(A2; ” “; B2) | =CONCAT(A2; ” “; B2) |
| Bob | Martin | =CONCATENER(A3; ” – “; B3) | =CONCAT(A3:B3) |
In the second example, CONCATENATE explicitly imposes the separator ” – “, while CONCAT(range reference) brushes through each cell without punctuation. The flexibility is less, but the speed of entry is undeniable.
Performance and limits
- Number of arguments: up to 255 for CONCATENATE, unlimited (range) for CONCAT.
- Readability: CONCAT significantly lightens the formula, especially on long ranges.
- Compatibility: CONCATENATE works on all versions of Excel, CONCAT requires Excel 2019/Office 365.
If your files are shared with users on older versions, prefer CONCATENATE to avoid #NAME? errors and favor TEXTJOIN for better control of separators.
Advanced alternatives: Power Query
When your needs go beyond simple concatenation of a few cells, consider using Power Query. In a few clicks, you merge columns from different tables, apply automatic separators, and refresh your query at will. The learning curve may seem more technical at first, but it ensures unmatched robustness when your data sources grow.
Good practices to avoid errors
- Always check that your recipients’ version supports CONCAT before using it extensively.
- Test on a reduced data set to quickly detect a missing separator or reversed order.
- If you need separation by a specific character (comma, semicolon), turn to TEXTJOIN for clarity.
- Briefly document your formula in a cell comment to facilitate maintenance.
FAQ
1. Why do I get #NAME? with CONCAT?
Because your version of Excel does not support this function. Update Office or temporarily switch to CONCATENATE.
2. Can we use an automatic separator with CONCAT?
No: for an integrated separator, TEXTJOIN remains the reference, with its dedicated “delimiter” argument.
3. What is the size limit for concatenated results?
Excel caps a cell at about 32,767 characters. Beyond that, you risk truncation.
4. Is it possible to combine CONCAT and basic database functions?
Yes: you can retrieve fields via connection to a SQL database and concatenate the result into a single string.
