Combining CONCAT, CONCATENATE, and TEXTJOIN in Excel

Course: Advanced Excel

Topic: Combining CONCAT, CONCATENATE, and TEXTJOIN

Course Link: Advanced Excel Course – JustAcademy

Demo Link: Register for a Free Course Demo

Website: JustAcademy Official Website

1. Introduction

In Microsoft Excel, text-combining functions are used to join words, names, numbers, dates, and values from multiple cells into a single text string. These functions are especially useful when preparing reports, creating full names, combining addresses, generating product descriptions, and organizing customer information.

Excel provides three important functions for joining text: CONCAT, CONCATENATE, and TEXTJOIN. Although they can perform similar tasks, they differ in syntax, flexibility, and the way they handle delimiters such as spaces, commas, hyphens, and other separators.

Understanding these functions helps Excel users create cleaner formulas, reduce manual work, and build dynamic reports that update automatically when the source data changes.

2. Learning Objectives

• Understand the purpose of CONCAT, CONCATENATE, and TEXTJOIN.
• Learn the syntax and arguments of each function.
• Combine text from multiple cells and ranges.
• Insert spaces, commas, hyphens, and custom delimiters.
• Understand how blank cells are handled by different functions.
• Apply text-combining functions to practical business scenarios.
• Identify common formula errors and troubleshoot them.

3. What Is Text Concatenation?

Text concatenation means joining two or more text values into one continuous string. The values can come from individual cells, ranges, numbers, or text entered directly into a formula.

For example, suppose cell A2 contains "Rahul" and cell B2 contains "Sharma". Combining these values with a space produces the full name "Rahul Sharma".

Text can be combined using functions or the ampersand (&) operator. The three functions covered in these notes provide different ways to achieve the same general objective.

4. Understanding the CONCAT Function

4.1 Definition

The CONCAT function combines text from multiple cells, text strings, or ranges into a single text string. It is the modern replacement for many basic CONCATENATE use cases and supports range references.

4.2 Syntax

=CONCAT(text1, [text2], ...)

• text1: The first text value, cell reference, or range to combine.
• text2: Additional optional text values, cell references, or ranges.
• Result: One combined text string.

4.3 Example: Combining First Name and Last Name

Suppose the worksheet contains the following data:

Formula:

=CONCAT(A2," ",B2)

Output: Rahul Sharma

The space enclosed in quotation marks separates the first name from the last name. Without this space, the output would be "RahulSharma".

4.4 Example: Combining Multiple Cells

Suppose A2 contains "Advanced", B2 contains "Excel", and C2 contains "Training". Use the following formula:

=CONCAT(A2," ",B2," ",C2)

Output: Advanced Excel Training

4.5 Example: Combining a Range

If cells A2:A5 contain A, B, C, and D, respectively, use:

=CONCAT(A2:A5)

Output: ABCD

CONCAT does not automatically insert spaces or punctuation between values. You must include separators in the formula whenever they are required.

4.6 Advantages of CONCAT

• Combines multiple cell references and ranges.
• Uses a straightforward syntax for joining text.
• Works well with dynamic worksheets and changing source values.
• Is useful for constructing names, labels, codes, and descriptions.

5. Understanding the CONCATENATE Function

5.1 Definition

CONCATENATE is an older Excel function used to join multiple text strings into one result. It remains useful when working with legacy spreadsheets or older Excel versions.

5.2 Syntax

=CONCATENATE(text1, [text2], ...)

• text1: The first text item or cell reference.
• text2: Additional text items or cell references.
• Result: A single combined text string.

5.3 Example: Combining Names

Assume A2 contains "Priya" and B2 contains "Patel". Use:

=CONCATENATE(A2," ",B2)

Output: Priya Patel

5.4 Example: Creating a Complete Address

Suppose the worksheet contains the following information:

Formula:

=CONCATENATE(A2,", ",B2,", ",C2,", ",D2)

Output: 101, MG Road, Mumbai, Maharashtra

5.5 Example: Creating a Product Code

Suppose A2 contains "PRD" and B2 contains "105". To generate a product identifier, use:

=CONCATENATE(A2,"-",B2)

Output: PRD-105

5.6 Limitations of CONCATENATE

• It does not automatically insert spaces or delimiters.
• Long formulas can become difficult to maintain.
• It is less convenient for joining large ranges than modern alternatives.
• It does not provide a built-in option to ignore empty cells.

CONCATENATE remains supported in many Excel versions, but Microsoft generally recommends CONCAT or TEXTJOIN for new formulas where appropriate.

6. Understanding the TEXTJOIN Function

6.1 Definition

TEXTJOIN combines multiple text values using a specified delimiter. Its main advantage is that it can automatically skip empty cells when the ignore_empty argument is set to TRUE.

6.2 Syntax

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

• delimiter: The separator placed between text values, such as a space, comma, hyphen, or line break.
• ignore_empty: TRUE skips empty cells; FALSE includes empty text values as positions between delimiters.
• text1: The first text value, cell reference, or range.
• text2: Additional text values, cell references, or ranges.

6.3 Example: Combining Names with a Space

Suppose A2 contains "Amit", B2 contains "Kumar", and C2 contains "Verma". Use:

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

Output: Amit Kumar Verma

The formula joins the three cells using a space as the delimiter.

6.4 Example: Combining Values with Commas

Suppose cells A2:A5 contain Excel, Word, PowerPoint, and Outlook.

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

Output: Excel, Word, PowerPoint, Outlook

This is useful for preparing lists of skills, categories, products, or services in a single cell.

6.5 Example: Ignoring Blank Cells

Consider the following data:

Formula:

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

Output: Mumbai, Pune, Nashik

Because ignore_empty is TRUE, the blank cell does not produce an extra comma or an empty item in the result.

6.6 Example: Including Empty Positions

Using the same data, enter:

=TEXTJOIN(", ",FALSE,A2:D2)

The blank cell is not ignored, so the result contains an empty position between delimiters: "Mumbai, , Pune, Nashik".

6.7 Example: Using a Hyphen as a Delimiter

Suppose A2 contains "2026", B2 contains "10", and C2 contains "27". Use:

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

Output: 2026-10-27

For real dates, ensure the underlying values and number formats are handled correctly. If a cell contains an Excel date serial value, use TEXT to format it before joining when necessary.

6.8 Example: Joining Text with a Line Break

To place values on separate lines within one cell, use CHAR(10) as the delimiter:

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

Enable Wrap Text for the result cell to display the line breaks properly. This technique is useful for creating compact summaries and multi-line labels.

7. Comparison of CONCAT, CONCATENATE, and TEXTJOIN

8. Combining All Three Functions with the Same Dataset

Suppose the worksheet contains these values:

Using CONCAT:

=CONCAT(A2," ",B2," ",C2)

Result: Advanced Excel Course

Using CONCATENATE:

=CONCATENATE(A2," ",B2," ",C2)

Result: Advanced Excel Course

Using TEXTJOIN:

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

Result: Advanced Excel Course

All three formulas produce the same result for this dataset. However, TEXTJOIN is easier to maintain when joining a range and applying a common separator, particularly when some cells may be blank.

9. Combining Text with Numbers

Excel can combine text and numbers, but numbers with specific display formats may need to be converted using the TEXT function first.

Example: Creating an Invoice Label

Suppose A2 contains the invoice number 125 and B2 contains the amount 2500.5.

=CONCAT("Invoice #",A2," - Amount: ",TEXT(B2,"₹#,##0.00"))

Example output: Invoice #125 - Amount: ₹2,500.50

The TEXT function converts the amount to formatted text before CONCAT joins it with the other values. The currency symbol and number format can be adjusted to suit the report.

Example: Combining a Percentage with a Label

If A2 contains 0.85, use:

=CONCAT("Completion: ",TEXT(A2,"0%"))

Output: Completion: 85%

10. Combining Text with Dates

Dates in Excel are stored as serial numbers. When concatenated directly, a date may appear as a number rather than in a familiar date format. Use TEXT to control how the date is displayed.

Suppose A2 contains a valid Excel date representing 27 October 2026.

=CONCAT("Report Date: ",TEXT(A2,"dd-mmm-yyyy"))

Example output: Report Date: 27-Oct-2026

For a range of dates, choose a suitable format based on the intended audience and regional date conventions.

11. Practical Business Applications

11.1 Customer Name Preparation

Combine first, middle, and last names into a full name. TEXTJOIN can skip missing middle names automatically.

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

11.2 Address Preparation

Combine building number, street, city, and state using a comma-separated format.

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

11.3 Employee Skill Summary

Join skills from multiple cells into one readable field.

=TEXTJOIN(", ",TRUE,B2:F2)

11.4 Product Description Generation

Combine product name, category, and model number into a description.

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

11.5 Report Labels

Generate report titles that update when the underlying values change.

=CONCAT("Sales Report - ",A2," - ",TEXT(B2,"mmm yyyy"))

11.6 Unique Identifier Preparation

Combine department codes, employee IDs, or location identifiers using a consistent separator.

=CONCAT(A2,"-",TEXT(B2,"0000"))

This example preserves leading zeros in the formatted employee ID. Select the appropriate format for your organization's identifier rules.

12. Common Errors and Troubleshooting

13. Best Practices

• Use CONCAT for straightforward combinations of text values and ranges.
• Prefer TEXTJOIN when you need a consistent delimiter across a range.
• Use CONCATENATE mainly when maintaining older workbooks or supporting an existing formula structure.
• Include spaces and punctuation explicitly when using CONCAT or CONCATENATE.
• Set ignore_empty to TRUE when blank cells should not create empty list items.
• Use TEXT for dates, percentages, currency amounts, and numbers that require a specific display format.
• Test formulas with blank cells, unusual values, and different data lengths.
• Use cell references instead of hard-coded values whenever the source data may change.
• Keep identifiers and business-critical codes consistent with organizational formatting rules.
• Remember that the result is text; concatenated numbers generally cannot be used directly in arithmetic without conversion.

14. Hands-On Practice Exercises

Exercise 1: Full Name

Given First Name in A2 and Last Name in B2, write a formula that combines both with one space.

Suggested answer:

=CONCAT(A2," ",B2)

Exercise 2: Comma-Separated Skills

Given skills in B2:F2, combine them with commas and spaces while ignoring blank cells.

Suggested answer:

=TEXTJOIN(", ",TRUE,B2:F2)

Exercise 3: Product Code

Combine the prefix in A2 and the product number in B2 with a hyphen.

Suggested answer:

=CONCATENATE(A2,"-",B2)

Exercise 4: Formatted Sales Report

Assume A2 contains a region name and B2 contains a sales amount. Create a label with the amount displayed to two decimal places.

Suggested answer:

=CONCAT("Region: ",A2," | Sales: ",TEXT(B2,"#,##0.00"))

Exercise 5: Multi-Line Summary

Combine A2:A5 into one cell with each nonblank value on a new line.

Suggested answer:

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

Enable Wrap Text to view the output correctly.

15. Mini Project: Create a Customer Information Summary

Objective: Build a customer summary using text-combining functions.

Assume the worksheet contains the following columns:

Task 1: Generate the customer's full name.

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

Task 2: Generate the customer's location.

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

Task 3: Create a complete customer summary.

=TEXTJOIN(" | ",TRUE,TEXTJOIN(" ",TRUE,A2:B2),TEXTJOIN(", ",TRUE,C2:D2),E2)

Example output: Rahul Sharma | Mumbai, Maharashtra | Premium

Expected learning outcome: By completing this project, learners practice combining multiple fields, handling blank values, using custom delimiters, and generating report-ready text from structured data.

16. Interview Questions and Answers

Q1. What is the purpose of the CONCAT function?

Answer: CONCAT joins text values, cell references, and ranges into one text string.

Q2. What is the difference between CONCAT and CONCATENATE?

Answer: Both combine text. CONCAT is the modern function and supports range references more conveniently, while CONCATENATE is the older function retained for compatibility.

Q3. What makes TEXTJOIN different from CONCAT?

Answer: TEXTJOIN includes a delimiter argument and an option to ignore empty cells, making it useful for creating readable lists from ranges.

Q4. How do you combine two names with a space?

Answer: Use =CONCAT(A2," ",B2) or =TEXTJOIN(" ",TRUE,A2:B2).

Q5. How can you skip blank cells while joining text?

Answer: Use TEXTJOIN with TRUE as the ignore_empty argument, for example =TEXTJOIN(", ",TRUE,A2:A10).

Q6. Can these functions combine numbers and dates?

Answer: Yes. However, numbers and dates may need to be formatted with TEXT to preserve the intended appearance.

Q7. Does CONCAT automatically add spaces between values?

Answer: No. Spaces and other delimiters must be provided explicitly.

Q8. When should TEXTJOIN be preferred?

Answer: Use TEXTJOIN when joining multiple values with a consistent delimiter, particularly when the source range may contain blanks.

Q9. What happens if ignore_empty is FALSE?

Answer: Empty cells are not skipped, so adjacent delimiters may appear around empty positions.

Q10. Can concatenated results be used in calculations?

Answer: A concatenated result is text. If a numeric result is required, use a suitable numeric formula or convert the text carefully when appropriate.

17. Quick Revision Notes

• CONCAT: Joins text values and ranges into one string.
• CONCATENATE: Older function for joining multiple text values.
• TEXTJOIN: Joins text using a chosen delimiter and can ignore blank cells.
• Space separator: Use " " inside quotation marks.
• Comma separator: Use ", " for a comma followed by a space.
• Hyphen separator: Use "-".
• Ignore blanks: Set TEXTJOIN's second argument to TRUE.
• Format values: Use TEXT for dates, percentages, currency, and leading zeros.
• Dynamic formulas: Cell references allow combined results to update with source data.

18. Conclusion

CONCAT, CONCATENATE, and TEXTJOIN are valuable Excel functions for combining information into readable and organized text. CONCAT is a convenient modern option for joining text and ranges, CONCATENATE remains useful in legacy workbooks, and TEXTJOIN offers greater control over separators and empty cells.

For most new reporting tasks involving lists or ranges, TEXTJOIN is often the most convenient choice. For simple text combinations, CONCAT is usually straightforward. Understanding the differences among these functions helps users create efficient formulas, prepare professional reports, and automate repetitive data-cleaning tasks in Advanced Excel.

Learn Advanced Excel with JustAcademy

Explore the course to strengthen your Excel skills, practice formulas, and develop practical reporting techniques.

Explore the Advanced Excel Course

Register for a Course Demo

Visit JustAcademy