The CONCAT Function in Excel is a versatile tool used to combine or join the contents of two or more cells into one. Unlike its predecessor, the CONCATENATE Function, it offers greater flexibility and supports a range of cell references, including entire arrays.
At The Coding College, we aim to make learning Excel easy and practical for everyone.
Syntax of the CONCAT Function
The syntax for the CONCAT Function is:
=CONCAT(text1, [text2], …) - text1: (Required) The first item to combine. This could be text, a number, or a cell reference.
- text2, …: (Optional) Additional items to combine, up to 253 arguments.
Key Features of the CONCAT Function
- Simpler Syntax: No need for separators like ampersands (&).
- Supports Ranges: Allows you to combine ranges and arrays directly.
- No Delimiters by Default: Unlike TEXTJOIN, you need to manually add separators if required.
Practical Examples of the CONCAT Function
Example 1: Basic Text Concatenation
| First Name | Last Name |
|---|---|
| John | Doe |
| Jane | Smith |
Formula:
=CONCAT(A2, " ", B2) Result: “John Doe” (for the first row)
Example 2: Combining Ranges
| Item | Price (₹) |
|---|---|
| Apple | 50 |
| Banana | 20 |
Formula:
=CONCAT(A2:A3, " - ₹", B2:B3) Result: “Apple – ₹50 Banana – ₹20”
Using Delimiters with CONCAT
To add delimiters (e.g., spaces, commas, or line breaks), manually include them in the formula.
Example: Adding a Comma Separator
| City | Country |
|---|---|
| New York | USA |
| Tokyo | Japan |
Formula:
=CONCAT(A2, ", ", B2) Result: “New York, USA”
CONCAT vs. CONCATENATE
While both functions serve a similar purpose, CONCAT is more efficient:
| Feature | CONCAT | CONCATENATE |
|---|---|---|
| Supports Ranges | ✅ Yes | ❌ No |
| Compatibility | Excel 2016+ | All Versions |
| Simpler Syntax | ✅ Yes | ❌ No |
Using CONCAT with Dynamic Data
Example: Combining Data for Reports
| Product | Sales |
|---|---|
| Laptop | 1200 |
| Tablet | 800 |
Formula:
=CONCAT(A2, ": ", B2, " units") Result: “Laptop: 1200 units”
Common Errors in CONCAT
- #NAME?: Occurs in older Excel versions that don’t support CONCAT.
- #VALUE!: Happens if a non-text value isn’t properly formatted.
Why Learn the CONCAT Function?
- Boost Productivity: Quickly combine data from multiple cells.
- Dynamic Updates: Automatic changes when data updates.
- Professional Reports: Create clean and organized outputs.
Explore More Excel Functions
The CONCAT Function simplifies data manipulation, especially when dealing with large datasets. Pair it with functions like TEXTJOIN and LEFT/RIGHT for enhanced functionality.