CONCAT and TEXTJOIN are text functions used to combine (join) text from multiple cells into one cell. These functions are very useful when working with names, addresses, codes, or formatted text.
1. CONCAT Function
The CONCAT function joins text from different cells.
Structure:
=CONCAT(text1, text2, text3, ...)
You can also use ranges:
=CONCAT(A1:A5)
Example 1: Combining First and Last Name
If:
- A1 = Ali
- B1 = Khan
Formula:
=CONCAT(A1," ",B1)
Result:
Ali Khan
Notice we added " " (space) between the names.
Example 2: Combining Multiple Cells
If A1 to A3 contain:
Ali
Sara
Ahmed
=CONCAT(A1:A3)
Result:
AliSaraAhmed
(No spaces unless added manually)
2. TEXTJOIN Function
The TEXTJOIN function is more advanced.
It allows you to:
- Add a delimiter (separator like space, comma, dash)
- Ignore empty cells
Structure:
=TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)
- delimiter → Symbol to separate text (e.g., ” “, “, “)
- ignore_empty → TRUE or FALSE
- text → Cells or ranges
Example 1: Joining Names with Space
If:
- A1 = Ali
- B1 = Khan
=TEXTJOIN(" ", TRUE, A1, B1)
Result:
Ali Khan
Example 2: Joining List with Comma
If A1 to A3 contain:
Ali
Sara
Ahmed
=TEXTJOIN(", ", TRUE, A1:A3)
Result:
Ali, Sara, Ahmed
Difference Between CONCAT and TEXTJOIN
| CONCAT | TEXTJOIN |
|---|---|
| Simply joins text | Joins text with a separator |
| Does not automatically ignore empty cells | Can ignore empty cells |
| Basic function | More flexible and powerful |
When to Use CONCAT
- Simple text combination
- Small number of cells
When to Use TEXTJOIN
- Large ranges
- Adding commas or spaces automatically
- Ignoring blank cells
Why These Functions Are Important
They help you:
- Combine names
- Create full addresses
- Build formatted reports
- Prepare data for export
- Save time in text management
CONCAT and TEXTJOIN are essential tools for handling and organizing text efficiently in Excel.