Join Text in Excel

Learn how to join text in Excel using CONCATENATE, CONCAT, TEXTJOIN, and the & operator with simple and real-life examples.

When working in Excel, you often need to combine or join text from different cells. Whether it’s joining first and last names, adding units to numbers, or creating formatted labels, Excel gives you multiple ways to do it:

• CONCATENATE Function
• CONCAT Function
• TEXTJOIN Function
• & (ampersand) operator

In this beginner-friendly guide, we’ll explain all three methods with real-life examples, use cases, and tips to help you choose the right one.

---

1. CONCATENATE Function

Syntax:

=CONCATENATE(text1, text2, …)

• Joins multiple text values or cell contents into one.
• Available in all Excel versions before Excel 2016.

Example 1: Combine First and Last Name

First Name	Last Name
Rahul	Sharma

=CONCATENATE(A2, ” “, B2)

Result: Rahul Sharma

Real-Life Use Case:

Used in attendance sheets, ID card generation, or email creation (=CONCATENATE(A2, ".", B2, "@gmail.com")).

---

2. CONCAT Function (Modern Alternative)

Syntax:

=CONCAT(text1, text2, …)

• Replaces CONCATENATE in Excel 2016 and later.
• Works like CONCATENATE but also accepts ranges.

Example 2: Join Name and City

Name	City
Meena	Mumbai

=CONCAT(A2, “, “, B2)

Result: Meena, Mumbai

Real-Life Use Case:

Used to generate mailing lists like Meena, Mumbai, Karan, Delhi, etc.

---

3. TEXRJOIN Function (Available in Excel 2016 and late)

The TEXTJOIN function in Excel is used to combine text from multiple cells into one single cell, and add a separator (like a comma, space, dash, etc.) between each item.

It’s super handy when you want to join names, addresses, or any values in a clean and controlled way.

---

Syntax

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

• delimiter – What you want to put between each text (e.g., comma ",", space " ", dash "-").
• ignore_empty – TRUE or FALSE.
  • TRUE: It skips empty cells.
  • FALSE: It includes empty cells.
• text1, text2,… – The text or cell ranges you want to join.

---

Example 3: Combine Names with Space

A1: Ram
B1: Kumar
C1: Sharma

=TEXTJOIN(" ", TRUE, A1, B1, C1)

Result: Ram Kumar Sharma

👉 This joins all 3 names with a space between them and skips any blank cells.

---

Example 3.1: Combine Items with a Comma

A2: Apple
B2: Banana
C2: Mango

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

Result: Apple, Banana, Mango

👉 Great for creating a list in one cell.

---

Example 3.2: Skipping Empty Cells

A3: Apple
B3: (Blank)
C3: Mango

=TEXTJOIN(", ", TRUE, A3:C3)

Result: Apple, Mango

👉 TRUE tells Excel to ignore the blank cell in B3.

---

Example 3.3: Including Empty Cells

=TEXTJOIN(", ", FALSE, A3:C3)

Result: Apple, , Mango

👉 Since FALSE was used, Excel includes the blank in the result.

---

Real-Life Use Cases

• Combine full names from First, Middle, Last name columns.
• Create a list of subjects selected by a student.
• Merge address fields into one clean cell.
• Combine keywords or tags for SEO or blogs.

---

Things to Note

• Available in Excel 2016 and later (also in Excel 365).
• Works with cell ranges and individual values.
• Much smarter and cleaner than using A1 & " " & B1 & " " & C1.

---

4. Ampersand (&) Operator

Syntax:

=text1 & text2 & …

• A shortcut alternative to CONCATENATE/CONCAT.
• Very flexible and readable.

Example 4: Combine Product and Price

Product	Price
Shoes	₹1500

=A2 & ” costs ” & B2

Result: Shoes costs ₹1500

Real-Life Use Case:

Used in dynamic messages, such as:

=”Dear ” & A2 & “, your score is ” & B2

Output: Dear Ravi, your score is 82

---

4. Comparison Table

Feature	CONCATENATE	CONCAT	& Operator
Excel Version	Pre-2016	Excel 2016+	All Versions
Supports Ranges	❌	✅	❌
Easy to Type	❌	✅	✅
Flexible Formatting	✅	✅	✅
Deprecation Status	Deprecated	Recommended	Always Available

---

5. Bonus: Combine Text with Numbers and Units

Example 5: Format Quantity and Unit

Item	Qty
Sugar	5

=A2 & ” – ” & B2 & ” kg”

Result: Sugar - 5 kg

Here are two real-life examples of using the CONCAT Function and & operator with lookup scenarios in Excel—especially useful when you’re dealing with multiple lookup values in VLOOKUP, XLOOKUP, or INDEX-MATCH.

---

Example 6: Combine Multiple Lookup Keys for VLOOKUP

Scenario: You have a product table with both Category and Product Code as unique identifiers, and you want to find the Price.

Data Table (in range A2:C6):

Lookup Column	Category	Product Code	Price
ShoesSH001	Shoes	SH001	1500
ShoesSH002	Shoes	SH002	1700
BagsBG001	Bags	BG001	1000
BagsBG002	Bags	BG002	1200

Lookup Input:

Category	Product Code	Result
Shoes	SH002	1700

Formula using VLOOKUP with & operator: in H2 cell

=VLOOKUP(F2 & G2, A2:D6, 4, 0)

Result: 1700

Why it works: You’re creating a helper key by combining Category & Product Code both in your lookup and source range.

Also Read: VLOOKUP in Excel

---

Example 7: Merge Lookup Result with Description Using CONCAT

Scenario: You want to create a complete product label by combining the product name and its category fetched using a lookup.

Data Table (A2:C6):

Product Code	Product Name	Category
SH001	Running Shoes	Shoes
BG001	Travel Bag	Bags

Lookup Cell:

Product Code
SH001

Formula using CONCAT:

=CONCAT(VLOOKUP(E2, A2:C6, 2, 0), " - ", VLOOKUP(E2, A2:C6, 3, 0))

Result: Running Shoes - Shoes

Why it works: You’re combining two lookup results (name and category) into a single readable string.

---

Download Practice Materials

📥 Download Excel File

---

Summary

Excel offers three main ways to join or concatenate text:

• CONCATENATE: Legacy function (avoid in newer versions)
• CONCAT: Modern and supports ranges
• & operator: Quick and intuitive

Use them to simplify your reports, labels, messages, and dashboards.

FAQs

What’s Next?

In the next post, we’ll learn about the Subtotal and Aggregate Function in Excel