Have you ever had to assemble multiple cells containing names, dates, or codes without resorting to costly copy-pasting? This is often the case when preparing a customer directory, generating IDs, or constructing a complete postal address. Previously, I used the old CONCATENATE formula or the ampersand (&), but I admit these solutions lacked flexibility – especially when you want to ignore empty cells. Then Excel introduced CONCAT and my spreadsheet-addict life was never the same. In this article, we will dissect this function point by point, illustrate with concrete cases, and even compare it with some alternatives. Follow the guide, let’s go!
1. What is the CONCAT function?
The CONCAT function is a modern evolution of the old CONCATENATE formula. It is used to assemble multiple pieces of text (strings, cell values, formula results) into a single cell. Introduced in Excel 2019 and Office 365, it offers:
- A simplified syntax (no need to spell out CONCATENATE to the end).
- The ability to handle cell ranges directly.
- Better performance management when accumulating dozens or even hundreds of arguments.
In a word, CONCAT is ideal when it comes to “gluing” text without delimiter breaks. To go further on other Excel text functions (LEFT, RIGHT, MID, FIND…), you will see that CONCAT stands out for its flexibility and lightness.
1.1. Syntax and arguments
The general form of CONCAT can be summarized as:
=CONCAT(text1; [text2]; …)
Where each argument can be:
- A cell reference (A2, B3…)
- A range (C1:C5)
- A literal value placed between quotes (“-“).
Example:
=CONCAT(A2; ” “; B2; ” – “; C2)
Here, we assemble the content of A2 and B2 with a space, then a dash. Nothing simpler!
1.2. Comparison with TEXTJOIN and the ampersand (&)
CONCAT has its strengths but is not always the best choice:
| Function | Advantages | Limitations |
|---|---|---|
| CONCAT | Short syntax, accepts ranges | No automatic delimiter, does not skip empty cells |
| TEXTJOIN | Configurable delimiter, option to ignore blanks | A bit more verbose to write |
| & (ampersand) | Super fast to type, no dedicated function | Poor integration with ranges, impossible to manage a large number of elements without repetition |
If you need to automatically skip empty cells (e.g., when some fields are optional), TEXTJOIN is often preferable. However, CONCAT remains a must-have for simple and quick assemblies.
2. Practical examples
2.1. Merging first name and last name
Let’s imagine a client file with two columns: “First Name” in A2 and “Last Name” in B2. To display “First Name Last Name”, you type:
=CONCAT(A2; ” “; B2)
Soon, the entire column becomes more readable. No need to juggle with the IF formula to handle a missing first or last name: if a cell is empty, CONCAT simply displays a single space, sometimes corrected afterwards using the TRIM function.
2.2. Building a complete address
When you have several lines (number, street, postal code, city), the key is to create a template:
=CONCAT(A2; “, “; B2; CHAR(10); C2; ” “; D2)
Thanks to CHAR(10), you insert a line break inside the cell (activate the “Wrap Text” option). With one formula, your address takes shape:
- 123, Rue Victor Hugo
- 75003 Paris
2.3. Generating IDs or product codes
You can also combine dates (formatted) with static texts:
=CONCAT(“PROD-“; TEXT(E2; “yyyymmdd”); “-“; F2)
The result: PROD-20230715-A45. A bullet-proof method to trace the origin of a batch, automate the creation of references, without ever retyping a single value.
3. Advanced tips
3.1. Handling empty cells
Let’s imagine that column C (“Address complement”) is sometimes empty. To avoid an isolated comma, you can nest a test:
=CONCAT(A2; ” “; B2; IF(C2″”; CONCAT(“, “; C2); “”); “, “; D2)
Here, the IF function takes over to add “, complement” only if necessary.
3.2. Using with other formulas
CONCAT can be combined with VLOOKUP to retrieve a label and assemble it:
=CONCAT(“Client: “; VLOOKUP(G2; Clients!A:D; 2; FALSE))
This technique is ideal for enriching dynamic reports.
4. Common errors and how to fix them
- #VALUE! error appears if you pass an invalid argument (e.g. a 2D array).
Solution: check each range or use INDEX to extract a single column. - Truncated result when the destination cell is not wide enough.
Solution: widen the column or enable text wrapping. - Extra spaces when assembling empty cells.
Solution: clean with TRIM or add an IF condition.
5. When to choose CONCAT in your toolbox?
In summary, using the CONCAT function becomes relevant if:
- You have a small volume of arguments (less than 10–15) to assemble.
- You are looking for a quick and clear syntax.
- You do not want to systematically manage delimiters (in that case, TEXTJOIN takes over).
- You want to integrate your formulas into an automated report or a template.
To deepen other formatting and structuring techniques for your tables – for example learning how to merge and split cells correctly in Excel without losing data – feel free to explore our dedicated guides.
Quick FAQ
- Are CONCAT and CONCATENATE the same? No: CONCAT replaces CONCATENATE in recent versions, lighter and accepting ranges.
- Can a separator be added automatically? No, you must include it manually or switch to TEXTJOIN.
- What is the argument limit? Up to 253 elements, but the formula remains more readable if you use fewer than 20.
- Does it work on Mac and Windows? Yes, from Excel 2019 and Office 365 onwards.
Conclusion
The CONCAT function in Excel is a major asset for anyone who wants to automatically assemble text without complicating their life. Whether you are building addresses, IDs, or labels, it will save you valuable time and simplify your formulas. Don’t hesitate to combine it with TEXTJOIN, the IF formula, or VLOOKUP for advanced results. Try it, test it, and you will see that your spreadsheets have never been so clean and efficient!