Mastering Text Joining in Excel: CONCATENATE, CONCAT, TEXTJOIN & Ampersand (&)

When working with Excel, combining text from multiple cells is a common task. Whether you're merging first and last names, creating structured sentences, or formatting reports, Excel offers several functions to make text joining efficient. In this article, we’ll explore four key methods: CONCATENATE, CONCAT, TEXTJOIN, and the & (ampersand) operator.

1. CONCATENATE (Old but Still in Use)

The CONCATENATE function was traditionally used to combine text values from multiple cells. Although replaced by CONCAT in newer Excel versions, it is still widely used.

Syntax:

=CONCATENATE(A2, " ", B2)        

Example: If A2 contains "John" and B2 contains "Doe", the result is: John Doe.

⚠ Limitation: CONCATENATE does not support ranges; you must select each cell individually.

2. CONCAT (The Modern Replacement)

Excel introduced CONCAT in Excel 2016 and later versions as an improved version of CONCATENATE.

Syntax:

=CONCAT(A2:B2)        

Example: If A2 has "John" and B2 has "Doe", the result is: JohnDoe.

✅ Advantage: Unlike CONCATENATE, CONCAT supports ranges, making it more efficient.

3. TEXTJOIN (The Most Powerful Option)

TEXTJOIN takes text joining to the next level by allowing a delimiter and the option to ignore empty cells.

Syntax:

=TEXTJOIN(" ", TRUE, A2:C2)        

Example: If A2 contains "John", B2 is empty, and C2 contains "Doe", the result is: John Doe.

✅ Why Use TEXTJOIN?

• Allows a separator (comma, space, or any character of choice).
• Can ignore empty cells to avoid unnecessary delimiters.
• Works well for dynamic data where not all cells may be filled.

4. Ampersand (&) – The Simplest Way

For quick text joining, the & operator is a handy alternative.

Syntax:

=A2 & " " & B2        

Example: If A2 = "John" and B2 = "Doe", the result is: John Doe.

✅ Why Use Ampersand?

• No function needed, making it simple and intuitive.
• Works well for quick concatenation in formulas.

Which One Should You Use?

CONCATENATE Older versions of Excel

CONCAT Simple text joining with ranges

TEXTJOIN Advanced joining with delimiters and ignoring blanks

& (Ampersand) Quick and simple text joining

Each method has its strengths, and choosing the right one depends on your needs.

Final Thoughts

Text joining in Excel is easier than ever with modern functions like CONCAT and TEXTJOIN. Whether you’re cleaning data, preparing reports, or automating tasks, mastering these functions will save you time and effort.

📢 Which function do you use the most? Let me know in the comments!

🔔 Follow me for more Excel tips and tricks!