CONCAT and TEXTJOIN

 

Step-by-Step Process to Use CONCAT and TEXTJOIN Functions

🔹 Method 1: Using CONCAT to Merge Data Without a Separator

Scenario: Combining First and Last Names

🔹 Sample Data (A2:B6)

First Name	Last Name
Rahul	Sharma
Meera	Gupta
Amit	Verma
Sanya	Singh
Mohan	Kapoor

🔹 Formula to Combine First and Last Names Without a Space 

=CONCAT(A2,B2)

➡ This merges "Rahul" and "Sharma" into "RahulSharma".

✔ Solution to Add a Space Manually 

=CONCAT(A2, " ", B2)

➡ This results in "Rahul Sharma".

---

🔹 Method 2: Using TEXTJOIN to Merge Data with a Separator

Scenario: Creating a List of Email Addresses with a Separator

🔹 Sample Data (A2:A6)

Name
Rahul
Meera
Amit
(Empty)
Sanya

🔹 Formula to Combine Names with a Comma Separator and Ignore Blanks 

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

➡ The result will be:
Rahul, Meera, Amit, Sanya (ignores empty cells).

✔ Alternative Example: Joining Full Names from Two Columns 

=TEXTJOIN(" - ", TRUE, A2:A6, B2:B6)

➡ The result will be:
Rahul Sharma - Meera Gupta - Amit Verma - Sanya Singh

---

🔍 Key Differences Between CONCAT and TEXTJOIN

Feature	CONCAT	TEXTJOIN
Separator Support	❌ No separator (must be added manually)	✅ Allows a custom separator
Handles Empty Cells	✅ Includes empty cells in result	✅ Can ignore empty cells
Best For	Merging data without separators	Merging data with separators
Complexity	Simple	More flexible

---

🎯 Key Takeaways

✔ Use CONCAT for basic merging without separators.
✔ Use TEXTJOIN when you need a delimiter and to ignore blanks.
✔ TEXTJOIN is more advanced, making it better for reports and clean lists.

Would you like an Excel file with these formulas for practice? 😊