How to Concatenate in Excel: A Complete Beginner-to-Pro Guide (2026)
Microsoft Excel is not just about numbers—it is equally powerful when working with text. One of the most common and useful text operations in Excel is concatenation, which simply means joining text from multiple cells into one. Whether you are merging first and last names, combining addresses, generating IDs, or preparing clean datasets for analysis, concatenation is an essential Excel skill.
In this guide, you’ll learn all the modern and classic ways to concatenate in Excel, with examples, tips, and real-world use cases—updated for how Excel is used in 2026.
What Does Concatenate Mean in Excel?
To concatenate means to combine two or more text strings into a single text string.
Example:
- Cell A1:
Dhanraj - Cell B1:
Chakraborty
Result after concatenation:
Dhanraj Chakraborty
Excel provides multiple ways to do this, ranging from simple formulas to advanced functions.
Method 1: Using the Ampersand (&) Operator (Most Popular)
The ampersand (&) is the easiest and most flexible way to concatenate in Excel.
Basic Example:
=A1 & B1
Adding a Space Between Words:
=A1 & " " & B1
Output:
Dhanraj Chakraborty
Why use &?
- Simple and fast
- Works in all Excel versions
- Easy to read and edit
✅ Best choice for beginners and professionals alike
Method 2: CONCAT Function (Modern Replacement)
Excel introduced the CONCAT function to replace the older CONCATENATE function.
Syntax:
=CONCAT(text1, text2, ...)
Example:
=CONCAT(A1, " ", B1)
Advantages:
- Cleaner than CONCATENATE
- Supports ranges
- Fully supported in Excel 2026
📌 Use CONCAT when you prefer function-based formulas instead of symbols.
Method 3: CONCATENATE Function (Legacy)
This function still works but is no longer recommended.
Example:
=CONCATENATE(A1, " ", B1)
Why avoid it?
- Not future-focused
- More typing
- Replaced by CONCAT
⚠️ Use only when working with very old Excel files.
Method 4: TEXTJOIN Function (Most Powerful)
TEXTJOIN is the most advanced and flexible concatenation function in Excel.
Syntax:
=TEXTJOIN(delimiter, ignore_empty, text1, ...)
Example:
=TEXTJOIN(" ", TRUE, A1, B1, C1)
Key Features:
- Automatically adds delimiters
- Can ignore empty cells
- Works with large datasets
Example Use Case:
Joining address parts:
=TEXTJOIN(", ", TRUE, A1, B1, C1, D1)
📌 Best for data cleaning and reporting
Method 5: Concatenating Numbers and Text
Excel treats numbers and text differently, so formatting matters.
Example:
="Total Sales: " & A1
If A1 = 5000, output:
Total Sales: 5000
Formatting Numbers:
="Total Sales: ₹" & TEXT(A1, "#,##0")
Output:
Total Sales: ₹5,000
Method 6: Concatenate Dates Properly
Dates must be formatted using the TEXT function.
Example:
="Date: " & TEXT(A1, "dd-mmm-yyyy")
Output:
Date: 12-Feb-2026
Method 7: Concatenate Entire Ranges
Using TEXTJOIN, you can merge a full column.
=TEXTJOIN(", ", TRUE, A1:A10)
This is extremely useful for:
- Logs
- Reports
- AI-ready datasets
- CSV preparation
Common Concatenation Errors (and Fixes)
❌ Missing Spaces
=A1 & B1
✔ Fix:
=A1 & " " & B1
❌ Numbers Showing as Text
✔ Use:
=VALUE(A1)
or format correctly with TEXT().
❌ Extra Commas or Gaps
✔ Use:
=TEXTJOIN(", ", TRUE, A1:A10)
Real-World Use Cases
- Merging first name + last name
- Creating email IDs
- Combining city, state, pin code
- Generating product SKUs
- Preparing data for AI & ML models
- Cleaning CRM and ERP exports
Best Method Comparison (2026)
| Method | Best For |
|---|---|
& |
Quick, simple joins |
| CONCAT | Modern replacement |
| CONCATENATE | Legacy files |
| TEXTJOIN | Large datasets & cleaning |
Pro Tips for Excel Users
✅ Always use TEXTJOIN for professional datasets
✅ Use TEXT() for formatting numbers & dates
✅ Avoid CONCATENATE in new projects
✅ Combine concatenation with TRIM() and CLEAN() for perfect text
Final Thoughts
Concatenation in Excel may look simple, but mastering it can dramatically improve your productivity, data quality, and reporting accuracy. In 2026, Excel continues to evolve, but text manipulation remains a core skill for students, analysts, developers, and AI professionals alike.
If you work with data—even occasionally—learning how to concatenate properly in Excel is non-negotiable.
