CONCATENATE Formula Generator (2026) — Join Text with Separator | Excel & Sheets
Joins several text strings into one text string.
CONCATENATE: Joining Text in Excel & Google Sheets
The CONCATENATE function combines text from multiple cells into one. It is indispensable for formatting names, addresses, and any data where you need to merge column values into a readable string.
Practical CONCATENATE Examples
Combine first and last name:
=CONCATENATE(A1, " ", B1)Build a full address:
=CONCATENATE(A1, ", ", B1, ", ", C1, " ", D1)Format a number with text:
=CONCATENATE("Total: $", TEXT(A1, "#,##0.00"))CONCATENATE vs TEXTJOIN vs Ampersand (&)
In modern Excel and Google Sheets, you have three options for joining text:
- CONCATENATE — Explicit arguments, works in all versions. Good for fixed-length joins.
- TEXTJOIN — Set a delimiter once and optionally skip empty cells. Ideal for variable-length lists.
- Ampersand (&) — Short syntax:
=A1&" "&B1. Clean for simple joins but less readable with many values.
Did You Know? CONCATENATE turns everything to text. If you need to preserve number formatting, always wrap the number in TEXT(value, "format") before concatenating.
Common Errors & Fixes
No space or wrong separator between values
Causes:- Forgetting to add a separator string between references.
- Using a number instead of quoted text for the separator.
Fixes:- Add ", " or " - " (or any separator in quotes) between each pair of values.
- Always put literal text in double quotes.
Frequently Asked Questions
How do I add a space or separator between concatenated values?
Include a string argument for the separator, e.g. ", " or " - " between cell references: =CONCATENATE(A1, " - ", B1).
What is the difference between CONCATENATE and TEXTJOIN?
CONCATENATE joins arguments in order; TEXTJOIN lets you specify a delimiter once and can ignore empty cells. In Excel 2016+ and Sheets, TEXTJOIN is often easier for lists.
Why does CONCATENATE show a number without formatting?
Concatenation turns numbers to plain text. Use TEXT() to format: =CONCATENATE(TEXT(A1,"0.00"), " ", B1).
How do I concatenate a date with text?
Wrap the date in TEXT() so it displays as you want: =CONCATENATE(TEXT(A1,"yyyy-mm-dd"), " ", B1).
Can I use CONCATENATE with more than 3 items?
Yes. Add more arguments: =CONCATENATE(A1, " ", B1, " ", C1). In Google Sheets and Excel 2016+, TEXTJOIN is simpler for many values.
Want to become an Excel Pro?
Stop searching for formulas. Master Excel in 30 days with this top-rated course.
Learn More