CONCATENATE() and TEXTJOIN() for Merging Text in Excel – A Practical Guide

 

Merging or combining text values from multiple cells is a common task in Excel. Whether you're building custom messages, formatting addresses, or cleaning data, combining text helps save time and create structured output.

Two of the most powerful functions for this task are CONCATENATE() and TEXTJOIN(). While both serve a similar purpose — joining text — they have some differences that can significantly affect how efficiently you work in Excel.

In this blog, we’ll explore:

• What CONCATENATE() does

• What TEXTJOIN() adds to the table

• Key differences

• Examples to make things clear

• Which one you should use

---

🔠 What is CONCATENATE()?

The CONCATENATE() function is used to join two or more strings of text together.

🔹 Syntax:

=CONCATENATE(text1, text2, ...)

Each argument (text1, text2, etc.) can be:

• A cell reference (like A1)

• A text string (like "Hello")

• A number (which will be converted to text)

✅ Example 1:

Assume:

• A1 contains: "Hello"

• B1 contains: "World"

Then,

=CONCATENATE(A1, B1)

Output:
"HelloWorld"

Want a space between them?

=CONCATENATE(A1, " ", B1)

Output:
"Hello World"

✅ Example 2: Concatenating First and Last Names

• A2: John

• B2: Doe

=CONCATENATE(A2, " ", B2)

Output:
"John Doe"

---

🔤 What is TEXTJOIN()?

TEXTJOIN() is a more advanced and flexible version introduced in Excel 2016 and later. It allows you to specify a delimiter (like a space, comma, or dash) between each text item and can also ignore blank cells automatically.

🔹 Syntax:

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

• delimiter: What to insert between each value (" ", ",", "-")

• ignore_empty: TRUE or FALSE (TRUE skips empty cells)

• text1, text2, …: The values or cell ranges to join

✅ Example 1: Join names with a space

• A2: John

• B2: (empty)

• C2: Doe

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

Output:
"John Doe"
(The blank middle cell is ignored)

If you use CONCATENATE(A2, " ", B2, " ", C2) here, you'd get:
"John Doe"
(with two spaces in the middle)

---

🔍 Key Differences Between CONCATENATE and TEXTJOIN

Feature	CONCATENATE()	TEXTJOIN()
Available from version	Excel 2007+	Excel 2016+
Supports delimiters directly	❌ No	✅ Yes
Can skip blank cells	❌ No	✅ Yes
Works with ranges	❌ No (must use cell by cell)	✅ Yes
Easier for large data sets	❌ Tedious	✅ Efficient
Preferred for modern Excel	❌ Outdated	✅ Recommended

---

💡 Practical Use Cases

🔸 1. Creating Full Address from Multiple Fields

Columns:

• A2: House No – 145

• B2: Street – Rose Ave

• C2: City – Mumbai

• D2: State – Maharashtra

Using CONCATENATE:

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

Using TEXTJOIN:

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

Result (both):
"145, Rose Ave, Mumbai, Maharashtra"

But the TEXTJOIN formula is much cleaner and flexible.

---

🔸 2. Merging Product Details

Columns:

• A2: Product Code – PRD123

• B2: Product Name – Power Bank

• C2: Capacity – 10000mAh

Using TEXTJOIN:

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

Result:
"PRD123 - Power Bank - 10000mAh"

---

🛠️ Advanced Tip – Combine with IF or FORMULAS

You can combine TEXTJOIN() with IF() for dynamic outputs.

✅ Example: Only include values if available

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

Or just use:

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

The ability to ignore empty cells with a simple TRUE argument makes TEXTJOIN() perfect for clean, readable outputs.

---

⚠️ Compatibility Notes

• CONCATENATE is being phased out in favor of TEXTJOIN() and CONCAT() in modern Excel versions.

• On Excel 365 and Excel 2021, use TEXTJOIN() for future-proof formulas.

• If working with older Excel versions (2013 or earlier), stick to CONCATENATE() or & operator.

---

#ExcelFormulas #TEXTJOIN #CONCATENATE #ExcelTips #AdminTools #FacilityManagement #DataMerging #ExcelFunctions #ProductivityWithExcel #ExcelAutomation