CONCAT: Modern Text Joining (2025)
Quick Answer: CONCAT joins text: =CONCAT(A1, " ", B1) or =CONCAT(A1:A10) for ranges. Modern replacement for CONCATENATE. Excel 2016+. No delimiter support - use TEXTJOIN for that. Simpler syntax than old CONCATENATE.
What is CONCAT?
CONCAT is Excel's modern text-joining function. It replaces CONCATENATE with simpler syntax and adds support for ranges. Available in Excel 2016 and later.
CONCATENATE vs CONCAT:
Old: CONCATENATE
=CONCATENATE(A1," ",B1," ",C1)
- • Each cell separately
- • No range support
- • Legacy function
- • Still works (backward compatibility)
New: CONCAT
=CONCAT(A1:C1)
- • Accepts ranges
- • Simpler syntax
- • Modern (Excel 2016+)
- • Microsoft recommended
Key Features:
✓ Range Support
=CONCAT(A1:A10) joins entire range
✓ Mixed References
Combine cells and text: =CONCAT(A1, " text")
✓ Multiple Ranges
=CONCAT(A1:A5, B1:B5)
✗ No Delimiter
Use TEXTJOIN for delimiters
Simple Example:
Data:
| A1 | John |
| B1 | Doe |
| C1 | Jr. |
=CONCAT(A1, " ", B1, " ", C1)
Result: John Doe Jr.
=CONCAT(A1:C1)
Result: JohnDoeJr. (no spaces)
⚠️ CONCAT vs TEXTJOIN
Use CONCAT when: Simple joining, no delimiter needed
Use TEXTJOIN when: Need delimiters (space, comma) or ignore blanks
=CONCAT(A1:A3) → "ABC"
=TEXTJOIN(", ", TRUE, A1:A3) → "A, B, C"
💡 Common Use Cases
- • Full names: First + Middle + Last name
- • Addresses: Street + City + State + ZIP
- • Product codes: Category + ID + Variant
- • File paths: Folder + Subfolder + Filename
- • Combined IDs: Multiple identifier fields
CONCAT Syntax
=CONCAT(text1, [text2], ...)
Arguments
text1 (required)
Text item or range to join. Can be text, number, cell reference, or range.
Cell reference: A1
Range: A1:A10
Text literal: "Hello"
Number: 123
text2, text3, ... (optional)
Additional text items to join (up to 255 arguments)
A1, " ", B1, " ", C1Common Patterns
Join Two Cells with Space
=CONCAT(A1, " ", B1)
Join Range (No Spaces)
=CONCAT(A1:A5)
Join Multiple Ranges
=CONCAT(A1:A3, " - ", B1:B3)
Add Prefix/Suffix
=CONCAT("ID: ", A1, " (Active)")
Mix Text and Numbers
=CONCAT("Order #", A1, " - $", B1)
Comparison: 3 Ways to Join
1. CONCAT (Modern, Range Support)
Best for: Simple joins, ranges
2. TEXTJOIN (Delimiter + Ignore Blanks)
Best for: Comma/space-separated lists
3. & Operator (Simple)
Best for: Quick 2-3 cell joins
⚠️ Important Notes
- • No automatic delimiters: CONCAT doesn't add spaces/commas automatically
- • Numbers convert: Numbers become text: 123 → "123"
- • Dates format: Dates show as serial numbers unless TEXT() wrapped
- • Empty cells: Empty cells contribute nothing (not even blank space)
- • Max length: Result limited to 32,767 characters
- • Errors: If any cell has error, CONCAT returns that error
10+ CONCAT Examples
Name Combinations
1. Full Name (First + Last)
=CONCAT(A2, " ", B2)
John + Smith → "John Smith"
2. Full Name with Middle Initial
=CONCAT(A2, " ", B2, ". ", C2)
John + M + Smith → "John M. Smith"
3. Last Name, First Name
=CONCAT(C2, ", ", A2, " ", B2)
"Smith, John M"
Address Formatting
4. Full Address (One Line)
=CONCAT(A2, ", ", B2, ", ", C2, " ", D2)
"123 Main St, Boston, MA 02101"
5. City, State ZIP
=CONCAT(B2, ", ", C2, " ", D2)
"Boston, MA 02101"
Product & Order IDs
6. Product SKU
=CONCAT(A2, "-", B2, "-", C2)
CAT + XL + RED → "CAT-XL-RED"
7. Order Number with Date
=CONCAT("ORD-", TEXT(TODAY(),"YYYYMMDD"), "-", A2)"ORD-20251203-1234"
8. Employee ID
=CONCAT("EMP", TEXT(A2,"0000"))123 → "EMP0123" (padded)
Range Operations
9. Join Entire Column
=CONCAT(A2:A10)
Joins all values in range (no spaces)
10. Join Row with Separator
=CONCAT(A2,"|",B2,"|",C2,"|",D2)
"Value1|Value2|Value3|Value4"
Special Formatting
11. Currency Format
=CONCAT("$", TEXT(A2,"#,##0.00"))1234.5 → "$1,234.50"
12. Date in Text
=CONCAT("Report for ", TEXT(A2,"mmmm yyyy"))"Report for December 2025"
🎯 Pro Tips
- • Use TEXT(): Format numbers/dates before concatenating
- • For delimiters: Use TEXTJOIN instead of CONCAT
- • Empty cells: CONCAT ignores them (no extra spaces)
- • Performance: CONCAT is faster than multiple & operators
- • Alternative: & operator for quick 2-3 cell joins
- • Max 255 arguments: But unlimited with ranges
Frequently Asked Questions
Can I use CONCAT in Excel 2013?
No. CONCAT requires Excel 2016, Excel 2019, Excel 365, or Excel for Mac 2016+. For older versions, use CONCATENATE function or & operator. Both work in all Excel versions and provide similar functionality.
How do I add spaces between cells with CONCAT?
Include space as separate argument: =CONCAT(A1, ' ', B1, ' ', C1). CONCAT doesn't add spaces automatically. Alternative: Use TEXTJOIN with space delimiter: =TEXTJOIN(' ', TRUE, A1:C1) for automatic spacing.
What's the difference between CONCAT and &?
& operator: Simple, works everywhere: =A1&' '&B1. CONCAT: Accepts ranges: =CONCAT(A1:A10). Use & for 2-3 cells. Use CONCAT for ranges or many cells. Both produce identical results. CONCAT is cleaner for complex joins.
Why does my CONCAT show numbers instead of dates?
Excel stores dates as numbers. CONCAT converts dates to serial numbers (45995 instead of 12/3/2025). Wrap dates in TEXT: =CONCAT('Order: ', TEXT(A1,'mm/dd/yyyy')). Or use format: TEXT(date, 'desired format').
Can CONCAT ignore blank cells?
CONCAT naturally ignores blank cells (contributes nothing). But can't skip specific values. For advanced ignore logic, use TEXTJOIN: =TEXTJOIN(' ', TRUE, A1:A10) ignores blanks AND adds spaces. CONCAT is simpler but less flexible.
What happens if CONCAT result is too long?
Excel text limit is 32,767 characters. CONCAT returns #VALUE! error if result exceeds this. Solution: Break into smaller chunks, use multiple columns, or reconsider data structure. Rarely an issue unless concatenating thousands of cells.