CONCAT, CONCATENATE and TEXTJOIN Practice Questions with Solutions

Introduction

Excel often stores related information in separate cells, such as first name and last name, city and state, product code and product name, or multiple phone numbers. CONCAT, CONCATENATE, and TEXTJOIN help combine this information into a single cell. In this chapter, you will practice these functions with names, addresses, product codes, employee details, email information, and other practical examples. CONCAT, CONCATENATE and TEXTJOIN Practice questions with solutions to help you understand the concepts.

Question 1: Combine First Name and Last Name Using CONCAT

Problem Statement

Combine the first name and last name into one full name.

Excel Data

Solution

In C2, enter:

=CONCAT(A2," ",B2)

Copy the formula down.

Expected Output

The " " adds a space between the first and last names.

Question 2: Combine Employee ID and Department

Problem Statement

Create a single employee reference using the employee ID and department.

Excel Data

The required format is:

101-IT

Solution

In C2, enter:

=CONCAT(A2,"-",B2)

Copy down.

Expected Output

Question 3: Combine Text Using CONCATENATE

Problem Statement

Create a complete sentence using the student’s name, course, and score.

Excel Data

Required Format

Rahul scored 85 in Excel.

Solution

In D2, enter:

=CONCATENATE(A2," scored ",C2," in ",B2,".")

Copy down.

Expected Output

CONCATENATE is an older Excel function. In newer Excel versions, CONCAT or TEXTJOIN is generally more flexible.

Question 4: Combine Address Components Using TEXTJOIN

Problem Statement

Combine the house number, street, city, state, and PIN code into one complete address.

Excel Data

Solution

In F2, enter:

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

Copy down.

Expected Output

TEXTJOIN is especially useful when you want to combine several cells using the same separator.

Question 5: Combine Multiple Skills Using TEXTJOIN

Problem Statement

An employee has several skills stored in separate columns. Combine all skills into one cell, separated by commas.

Excel Data

Solution

In E2, enter:

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

Copy down.

Expected Output

Question 6: TEXTJOIN with Blank Cells

Problem Statement

Some employees have only two skills while others have three. Combine the available skills without creating extra separators for blank cells.

Excel Data

Solution

In E2, enter:

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

Copy down.

Expected Output

The TRUE argument tells TEXTJOIN to ignore empty cells.

Question 7: Create a Product Code Using CONCAT

Problem Statement

A product code should contain:

• Category
• Product number
• Year

Required format:

ELEC-101-2026

Excel Data

Solution

In D2, enter:

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

Copy down.

Expected Output

Question 8: Create a Customer Information Sentence

Problem Statement

Combine the customer name, city, membership type, and purchase amount into a readable sentence.

Excel Data

Required Format

Rahul is a Premium customer from Delhi with a purchase of ₹15000.

Solution

In E2, enter:

=CONCAT(A2," is a ",C2," customer from ",B2," with a purchase of ₹",D2,".")

Copy down.

Expected Output

Question 9: Combine First, Middle and Last Names

Problem Statement

Some employees have a middle name and some do not. Combine the three name columns while automatically ignoring blank cells.

Excel Data

Solution

In D2, enter:

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

Copy down.

Expected Output

This is one of the practical advantages of TEXTJOIN: blank cells can be ignored automatically.

Question 10: Build a Complete Employee Profile Using TEXTJOIN

Problem Statement

Create a single employee profile using the following information:

• Employee name
• Department
• City
• Experience
• Skills

Each part should appear on a separate line inside the same cell.

Excel Data

Solution

In H2, enter:

=TEXTJOIN(CHAR(10),TRUE,"Name: "&A2,"Department: "&B2,"City: "&C2,"Experience: "&D2&" years","Skills: "&TEXTJOIN(", ",TRUE,E2:G2))

Copy down.

Turn on Wrap Text for column H so the lines appear properly.

Expected Output

For Rahul:

Name: Rahul
Department: IT
City: Delhi
Experience: 3 years
Skills: Excel, SQL, Python

For Priya:

Name: Priya
Department: HR
City: Noida
Experience: 5 years
Skills: Excel, Power BI

For Amit:

Name: Amit
Department: Sales
City: Jaipur
Experience: 2 years
Skills: Excel, CRM, Communication

This example combines TEXTJOIN, CHAR(10), and another TEXTJOIN to create a structured profile in a single cell.

Key Takeaways

• CONCAT combines text or values from multiple cells.
• CONCATENATE is an older function used to join text.
• TEXTJOIN combines multiple values using a delimiter such as a comma, space, or hyphen.
• TEXTJOIN can ignore blank cells when its second argument is TRUE.
• CONCAT is generally more flexible than the older CONCATENATE function.
• TEXTJOIN is particularly useful when combining a range of cells.
• You can use " " to add spaces between text values.
• You can use ", " to create comma-separated lists.
• CHAR(10) can be used with TEXTJOIN to place each item on a new line within a cell.
• These functions are useful for creating full names, addresses, product codes, employee profiles, descriptions, and combined reports.
• TEXTJOIN is especially useful when some cells in the source range may be blank.

FAQs

1. What does CONCAT do in Excel?

CONCAT combines text from multiple cells or values into a single result.

=CONCAT(A2," ",B2)

2. What is CONCATENATE in Excel?

CONCATENATE is an older Excel function that joins multiple text values.

=CONCATENATE(A2," ",B2)

In newer Excel versions, CONCAT and TEXTJOIN provide more flexible options.

3. What is the difference between CONCAT and TEXTJOIN in Excel?

CONCAT joins values but does not have a delimiter argument. TEXTJOIN lets you specify a delimiter and can optionally ignore blank cells.

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

4. How do I combine first and last names in Excel?

You can use CONCAT:

=CONCAT(A2," ",B2)

Or TEXTJOIN:

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

5. How do I combine cells with commas?

Use TEXTJOIN:

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

The first argument specifies the comma and space separator.

6. What does TRUE mean in TEXTJOIN?

The TRUE argument tells Excel to ignore empty cells.

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

If one of the cells is blank, Excel does not add an unnecessary delimiter for that blank cell.

7. Can TEXTJOIN combine cells from different columns?

Yes. You can specify individual cells or ranges.

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

8. Can TEXTJOIN put each item on a new line?

Yes. Use CHAR(10) as the delimiter.

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

You should enable Wrap Text to display the separate lines properly.

9. Which is better for combining many cells: CONCAT or TEXTJOIN?

For a simple combination, CONCAT is useful. When you need separators or want to ignore blank cells, TEXTJOIN is generally more suitable.

10. Can I combine TEXTJOIN with other Excel functions?

Yes. TEXTJOIN can be combined with functions such as IF, TRIM, UPPER, LOWER, PROPER, and other formulas to create more advanced text-processing solutions.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar