How to Use CONCAT and TEXTJOIN in Excel

Combining text from multiple cells is one of the most common tasks in Excel. Whether you are preparing reports, cleaning datasets, generating full names, or creating formatted output for dashboards, the ability to join text efficiently can save a significant amount of time. For many years, Excel users relied on the ampersand (&) operator or the CONCATENATE function. However, modern versions of Excel introduced CONCAT and TEXTJOIN, which provide more flexible, powerful, and user-friendly ways to combine text.

In this article, you will learn how to use CONCAT and TEXTJOIN, understand the differences between them, explore practical examples, and see how they compare with older text-combining methods. By the end, you will know when to use each function and how to apply them effectively in real-world Excel scenarios.

Understanding Text Concatenation in Excel

Text concatenation simply means joining multiple text values into one single text string. These values can come from:

• Individual cells
• Text typed directly into a formula
• Results of other formulas
• Numbers converted to text

For example, combining a first name and last name into a full name, or joining a city and country with a comma in between, are typical use cases.

Excel now offers two modern functions for this purpose:

• CONCAT – Best for straightforward text joining
• TEXTJOIN – Ideal when you need delimiters and want to ignore empty cells

The CONCAT Function: An Overview

The CONCAT function is a modern replacement for the older CONCATENATE function. It performs the same core task—joining text—but with improved syntax and better support for ranges.

Syntax of CONCAT

CONCAT(text1, [text2], …)

Arguments Explained

• text1 – The first text value or cell reference
• text2, … – Additional text values, cell references, or ranges

You can include up to 255 text arguments in a single CONCAT formula.

Basic Example of CONCAT

Imagine you have the following data:

To combine these into a full name:

=CONCAT(A2, " ", B2)

Result:

John Smith

In this example:

• A space " " is added manually between the first and last name
• CONCAT simply joins everything in the order provided

Using CONCAT with Ranges

One of the improvements over CONCATENATE is that CONCAT can handle ranges directly.

Example: Combining Multiple Cells

If cells A1:A5 contain text:

=CONCAT(A1:A5)

This will combine all values in the range into a single text string.

Important Note: CONCAT does not automatically add spaces or separators. If you need separators, you must include them manually or use TEXTJOIN instead.

CONCAT vs Ampersand (&)

The ampersand operator is still valid and widely used:

=A2 & " " & B2

While this works perfectly, CONCAT offers advantages:

• Cleaner syntax when combining many values
• Easier handling of ranges
• Better readability in complex formulas

For simple combinations, both methods are fine. For larger formulas, CONCAT is often more maintainable.

Limitations of CONCAT

While CONCAT is useful, it has some limitations:

• Cannot automatically insert delimiters between values
• Does not ignore empty cells
• Requires manual handling of spacing and punctuation

For more advanced text-joining needs, Excel provides TEXTJOIN.

The TEXTJOIN Function: An Overview

TEXTJOIN is a more powerful text-combining function introduced to handle real-world scenarios more efficiently. It allows you to define a delimiter and choose whether to ignore empty cells.

Syntax of TEXTJOIN

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Arguments Explained

• delimiter – The character(s) to insert between text values
• ignore_empty – TRUE or FALSE
• text1, text2, … – Text values or ranges to combine

Basic Example of TEXTJOIN

Using the same name example:

=TEXTJOIN(" ", TRUE, A2, B2)

Result:

John Smith

Here:

• " " is the delimiter (space)
• TRUE tells Excel to ignore empty cells
• You do not need to manually add spaces between values

Using TEXTJOIN with Ranges

TEXTJOIN truly shines when working with ranges.

Example: Combining a List with Commas

If A1:A5 contains city names:

=TEXTJOIN(", ", TRUE, A1:A5)

Result:

London, Paris, Berlin, Rome

Empty cells in the range will be automatically ignored, preventing unwanted extra commas.

Ignoring Empty Cells with TEXTJOIN

The ignore_empty argument is a major advantage.

Example

If your data contains blanks:

Formula:

=TEXTJOIN(", ", TRUE, A1:A4)

Result:

Apple, Banana, Orange

If you set ignore_empty to FALSE, Excel will include delimiters for empty cells, which often produces undesirable results.

TEXTJOIN with Line Breaks

You can use TEXTJOIN to create multi-line text within a single cell.

Example

=TEXTJOIN(CHAR(10), TRUE, A1:A5)

• CHAR(10) inserts a line break
• Enable Wrap Text to display lines properly

This is especially useful for notes, addresses, or exporting formatted text.

CONCAT vs TEXTJOIN: Key Differences

In practice:

• Use CONCAT for straightforward combinations
• Use TEXTJOIN when working with lists, separators, or inconsistent data

Practical Business Use Cases

1. Creating Full Names

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

Useful when middle names may be missing.

2. Formatting Addresses

=TEXTJOIN(", ", TRUE, A2, B2, C2, D2)

Automatically excludes blank address components.

3. Generating CSV-Style Output

=TEXTJOIN(",", FALSE, A1:D1)

Ideal for exporting data to text-based systems.

4. Combining Text with Numbers

=TEXTJOIN(" - ", TRUE, A2, TEXT(B2, "0.00"))

Works well with the TEXT function for formatting numbers.

Combining CONCAT and TEXTJOIN with Other Functions

Both functions are often used alongside:

• TEXT – Format numbers and dates
• IF / IFS – Conditional text logic
• TRIM – Clean extra spaces
• SUBSTITUTE – Replace unwanted characters

Example

=TEXTJOIN(" ", TRUE, IF(A2<>"", A2, ""), IF(B2<>"", B2, ""))

This ensures only valid text is joined.

Common Mistakes to Avoid

1. Forgetting delimiters in CONCAT CONCAT does not insert spaces automatically.

2. Using FALSE instead of TRUE in TEXTJOIN This can result in extra delimiters.

3. Not enabling Wrap Text for line breaks CHAR(10) will not display correctly otherwise.

4. Assuming compatibility with older Excel versions CONCAT and TEXTJOIN require Excel 2016 or newer (or Microsoft 365).

Compatibility and Version Notes

• Available in: Excel 2016+, Excel 2019, Excel 2021, Microsoft 365
• Not available in: Excel 2013 and earlier

For older versions, users must rely on:

• CONCATENATE
• & operator

Best Practices for Text Joining in Excel

• Use TEXTJOIN when dealing with lists or optional values
• Keep formulas readable by limiting unnecessary nested functions
• Always test formulas with empty cells
• Combine with TRIM to clean imported data
• Use named ranges for better clarity

Conclusion

The CONCAT and TEXTJOIN functions represent a significant improvement in Excel’s text-handling capabilities. CONCAT offers a clean and modern way to join text values and ranges, making it ideal for simple use cases. TEXTJOIN goes a step further by allowing delimiters and ignoring empty cells, which makes it indispensable for real-world data preparation and reporting.

By understanding the strengths and limitations of each function, you can choose the right tool for the task and write cleaner, more efficient Excel formulas. Mastering these functions will not only improve your productivity but also help you create more professional and reliable spreadsheets.

As you continue learning Excel, CONCAT and TEXTJOIN will quickly become essential tools in your formula toolkit.