CONCATENATE in Excel: Complete Guide with Examples (2025)
Quick Answer: CONCATENATE joins text: =CONCATENATE(A1," ",B1). Modern alternative: =CONCAT(A1," ",B1) or =TEXTJOIN(" ", TRUE, A1:B1). Use CONCAT for Excel 2016+.
What is CONCATENATE?
CONCATENATE is an Excel function that joins (concatenates) text strings from multiple cells into one cell. While still functional, it has been replaced by newer functions like CONCAT and TEXTJOIN in Excel 2016+.
Basic Example:
=CONCATENATE(A1, B1)
Result: "JohnDoe"
=CONCATENATE(A1, " ", B1)
Result: "John Doe"
=CONCATENATE("Hello ", A1, "!")
Result: "Hello John!"⚠️ Important Note:
CONCATENATE is a legacy function. Microsoft recommends using these modern alternatives:
- •CONCAT - Simpler syntax, supports ranges (Excel 2016+)
- •TEXTJOIN - Auto-adds delimiters, skips blanks (Excel 2016+)
- •& operator - Fastest for simple combinations (all versions)
CONCATENATE (Old)
=CONCATENATE(A1," ",B1)
Legacy, still works
CONCAT (New)
=CONCAT(A1," ",B1)
Modern, supports ranges
TEXTJOIN (Best)
=TEXTJOIN(" ",TRUE,A1:B1)Auto-delimiter, skips blanks
💡 When to Use Each:
- •Use CONCATENATE: Only for compatibility with Excel 2013 or earlier
- •Use CONCAT: For modern Excel (2016+), simple combining
- •Use TEXTJOIN: When you need delimiters or want to skip blanks
- •Use & operator: For quick, simple combinations
CONCATENATE Syntax & Comparison
CONCATENATE Syntax:
=CONCATENATE(text1, [text2], ...)
Parameters:
- text1: Required. First text string
- text2: Optional. Additional text strings (up to 255)
Examples:
=CONCATENATE(A1, B1) → "JohnDoe"
=CONCATENATE(A1, " ", B1) → "John Doe"
=CONCATENATE("Mr. ", A1, " ", B1) → "Mr. John Doe"Function Comparison:
| Feature | CONCATENATE | CONCAT | TEXTJOIN |
|---|---|---|---|
| Excel Version | All versions | 2016+ | 2016+ |
| Supports Ranges | ✗ No | ✓ Yes | ✓ Yes |
| Auto-Delimiter | ✗ No | ✗ No | ✓ Yes |
| Skip Blanks | ✗ No | ✗ No | ✓ Yes |
| Status | Legacy | Current | Current |
Same Result, Different Methods:
Goal: Combine "John" and "Doe" with space
CONCATENATE (Old Way):
=CONCATENATE(A1, " ", B1)
CONCAT (Modern):
=CONCAT(A1, " ", B1)
TEXTJOIN (Best):
=TEXTJOIN(" ", TRUE, A1, B1)
& Operator (Fastest):
=A1&" "&B1
All return: "John Doe"✗ CONCATENATE Limitations:
- •Cannot use ranges:
=CONCATENATE(A1:A10)doesn't work - •Must list each cell individually:
=CONCATENATE(A1,A2,A3,...) - •No automatic delimiter insertion
- •Includes blank cells (no skip option)
Practical Examples
Example 1: Full Names
A B C 1 First Last Full Name 2 John Doe = =CONCATENATE(A2," ",B2) → "John Doe" 3 Jane Smith = =CONCATENATE(A3," ",B3) → "Jane Smith" Modern alternative: =CONCAT(A2," ",B2) =A2&" "&B2
Example 2: Address Lines
A B C D
1 Street City State Full Address
2 123 Main Boston MA = =CONCATENATE(A2,", ",B2,", ",C2)
→ "123 Main, Boston, MA"
Better with TEXTJOIN:
=TEXTJOIN(", ", TRUE, A2:C2)Example 3: Product SKU
A B C D
1 Category Type Year SKU
2 ELEC TV 2024 = =CONCATENATE(A2,"-",B2,"-",C2)
→ "ELEC-TV-2024"
Alternative:
=A2&"-"&B2&"-"&C2Example 4: Greeting Message
A B
1 Name Greeting
2 John = =CONCATENATE("Hello, ",A2,"! Welcome to our store.")
→ "Hello, John! Welcome to our store."
Alternative:
="Hello, "&A2&"! Welcome to our store."Example 5: Error Handling
Common Error: Missing commas ❌ Wrong: =CONCATENATE(A1 B1) ✓ Correct: =CONCATENATE(A1,B1) Common Error: Using ranges ❌ Wrong: =CONCATENATE(A1:A10) ✓ Correct: =CONCATENATE(A1,A2,A3,A4,A5,A6,A7,A8,A9,A10) ✓ Better: =CONCAT(A1:A10) Common Error: Forgetting quotes ❌ Wrong: =CONCATENATE(A1, ,B1) ✓ Correct: =CONCATENATE(A1," ",B1)
✓ Migration Guide:
If you have existing CONCATENATE formulas, here's how to upgrade:
Old CONCATENATE:
=CONCATENATE(A1," ",B1," ",C1)
Replace with CONCAT:
=CONCAT(A1," ",B1," ",C1)
Or use TEXTJOIN:
=TEXTJOIN(" ", TRUE, A1:C1)
Or use & operator:
=A1&" "&B1&" "&C1Frequently Asked Questions
What is CONCATENATE in Excel?
CONCATENATE is an Excel function that joins text from multiple cells into one. Syntax: =CONCATENATE(text1, text2, ...). Example: =CONCATENATE(A1, " ", B1) combines cells with space. Note: CONCAT is the modern replacement (Excel 2016+).
What's the difference between CONCATENATE and CONCAT?
CONCATENATE is older, requires individual cells: =CONCATENATE(A1,A2,A3). CONCAT is newer (Excel 2016+), supports ranges: =CONCAT(A1:A10). Both combine text. Use CONCAT for modern Excel, CONCATENATE for compatibility with older versions.
Is CONCATENATE deprecated in Excel?
CONCATENATE still works but is replaced by CONCAT (Excel 2016+). Microsoft recommends using CONCAT or TEXTJOIN for new workbooks. CONCATENATE remains for backward compatibility. Use CONCAT for better functionality.
How do I use CONCATENATE with spaces?
Add " " as a parameter: =CONCATENATE(A1," ",B1) adds space between values. Example: =CONCATENATE("John"," ","Doe") returns "John Doe". Or use & operator: =A1&" "&B1.
Can CONCATENATE work with ranges?
No, CONCATENATE doesn't support ranges. Must list each cell: =CONCATENATE(A1,A2,A3). Use CONCAT for ranges: =CONCAT(A1:A10). Or use TEXTJOIN with delimiter: =TEXTJOIN(" ", TRUE, A1:A10).
What should I use instead of CONCATENATE?
Use CONCAT (Excel 2016+) for simple combining: =CONCAT(A1:A10). Use TEXTJOIN for delimiter: =TEXTJOIN(", ", TRUE, A1:A10). Use & operator for quick combinations: =A1&" "&B1. All are better than CONCATENATE.
Why is my CONCATENATE not working?
Common issues: 1) Missing commas between parameters 2) Using ranges (not supported) 3) Missing quotes around text 4) Cell contains error. Check syntax: =CONCATENATE(text1, text2, ...). Each parameter needs comma separator.
How many arguments can CONCATENATE take?
CONCATENATE accepts up to 255 arguments in Excel 2007+. Earlier versions limited to 30. Each argument can be text, number, or cell reference. For many values, use CONCAT or TEXTJOIN instead.