Introduction
Mastering the Excel CONCAT skill is essential for combining text data efficiently. In this guide, you’ll practice 20 real-world CONCAT and TEXTJOIN problems with solutions, helping you build confidence in handling Excel text functions
In this guide, we’ll cover how CONCAT and TEXTJOIN work, where to use them, and provide 20 practice problems with solutions to help you master text joining. A free downloadable Excel file is also available.
What is CONCAT in Excel?
The CONCAT function merges text from different cells into one cell.
Syntax:
=CONCAT(text1, [text2], …)- Replaces the old CONCATENATE function
- Works with ranges (e.g.,
=CONCAT(A2:C2)) - Does not allow delimiters automatically
Example:
If A2 = “John”, B2 = “Smith”,
=CONCAT(A2," ",B2) → John SmithWhat is TEXTJOIN in Excel?
The TEXTJOIN function combines text from multiple cells and allows you to specify a delimiter (e.g., space, comma, dash). It can also ignore blank cells.
Syntax:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)- Adds delimiters automatically
- Can skip empty cells
- Works with ranges
Example:
If A2 = “North”, B2 = “ProductA”, C2 = “500”
=TEXTJOIN(" - ",TRUE,A2,B2,C2) → North – ProductA – 500
CONCAT vs TEXTJOIN: Key Difference
| Feature | CONCAT | TEXTJOIN |
|---|---|---|
| Delimiter support | No | Yes |
| Ignore blanks | No | Yes |
| Range support | Yes | Yes |
| Best use case | Simple joins | Complex joins with delimiters |
Example Dataset
| First_Name | Last_Name | Department | Employee_ID | Country |
|---|---|---|---|---|
| John | Doe | Finance | E001 | USA |
| Mary | Smith | HR | E002 | UK |
| Sam | Brown | Sales | E003 | Canada |
| Rita | White | IT | E004 | India |
| Alex | Black | Finance | E005 | Germany |
| Nina | Green | HR | E006 | France |
| Tom | King | Sales | E007 | Japan |
| Sara | Taylor | IT | E008 | Brazil |
| David | Lee | Finance | E009 | China |
| Lily | Adams | HR | E010 | Australia |
20 Excel CONCAT Skill Problems with Solutions
1. Combine first and last name into full name using CONCAT
Formula:
=CONCAT(A2," ",B2)
Result: John Doe
2. Combine first and last name into full name using TEXTJOIN
Formula:
=TEXTJOIN(" ",TRUE,A2,B2)
Result: John Doe
3. Create email ID from first name and department
Formula:
=LOWER(CONCAT(A2,".",C2,"@company.com"))
Result: [email protected]
4. Create unique identifier with Employee ID and Last Name
Formula:
=CONCAT(D2,"-",B2)
Result: E001-Doe
5. Combine first name, last name, and country
Formula:
=TEXTJOIN(", ",TRUE,A2,B2,E2)
Result: John, Doe, USA
6. Generate display name as “Last, First”
Formula:
=CONCAT(B2,", ",A2)
Result: Doe, John
7. Add department and country into one string
Formula:
=TEXTJOIN(" - ",TRUE,C2,E2)
Result: Finance – USA
8. Make user code with first 3 letters of first name and Employee ID
Formula:
=CONCAT(LEFT(A2,3),D2)
Result: JohE001
9. Create sentence: “John works in Finance”
Formula:
=CONCAT(A2," works in ",C2)
Result: John works in Finance
10. Build directory listing as “John Doe (Finance – USA)”
Formula:
=CONCAT(A2," ",B2," (",C2," - ",E2,")")
Result: John Doe (Finance – USA)
11. Join first 3 characters of First Name and Last Name
Formula:
=CONCAT(LEFT(A2,3),B2)
Result: JohDoe
12. Create login name as lowercase first initial + last name
Formula:
=LOWER(CONCAT(LEFT(A2,1),B2))
Result: jdoe
13. Combine department and employee ID with underscore
Formula:
=CONCAT(C2,"_",D2)
Result: Finance_E001
14. Generate ID with First Name and first 2 letters of Country
Formula:
=CONCAT(A2,LEFT(E2,2))
Result: JohnUS
15. Join first name, last name and Employee ID separated by dashes
Formula:
=TEXTJOIN("-",TRUE,A2,B2,D2)
Result: John-Doe-E001
16. Build sentence: “Sam from Sales works in Canada”
Formula:
=CONCAT(A4," from ",C4," works in ",E4)
Result: Sam from Sales works in Canada
17. Make formatted string: “[E001] John Doe – Finance”
Formula:
=CONCAT("[",D2,"] ",A2," ",B2," - ",C2)
Result: [E001] John Doe – Finance
18. Join all fields into one line
Formula:
=TEXTJOIN(" | ",TRUE,A2,B2,C2,D2,E2)
Result: John | Doe | Finance | E001 | USA
19. Create shortened directory code with First Initial and Country
Formula:
=CONCAT(LEFT(A2,1),"-",E2)
Result: J-USA
20. Make profile string: “John | Doe | HR | USA”
Formula:
=TEXTJOIN(" | ",TRUE,A2,B2,C2,E2)
Result: John | Doe | Finance | USA
Best Practices for CONCAT / TEXTJOIN
- Use TEXTJOIN instead of CONCAT for complex reports.
- Always set ignore_empty = TRUE in TEXTJOIN to skip blanks.
- Use delimiters (space, comma, dash) to improve readability.
- Combine with IF, LEFT, RIGHT for advanced logic.
