I first needed CONCATENATE when I had a spreadsheet with first names in one column and last names in another, and I needed a single “Full Name” column for a mail merge. Copying and retyping hundreds of names manually wasn’t an option, so I went looking for a way to combine text automatically. CONCATENATE was the answer, and it’s stayed a core part of my toolkit ever since — even as Excel has introduced newer alternatives.
Here’s a full breakdown of how it works and how to use it well.
What Is the CONCATENATE Function in Excel?
CONCATENATE joins two or more text strings into a single string. It can combine text from different cells, plain text you type directly, numbers, and even results from other formulas, all into one combined value. It’s especially useful for building full names, addresses, IDs, or any composite text field from separate pieces of data.
It’s worth noting that Microsoft now recommends the newer CONCAT function or the “&” operator as modern alternatives, but CONCATENATE still works in all current versions of Excel and remains widely used and taught.
Required Data
To use CONCATENATE, you need:
- Two or more pieces of text, which can be cell references, typed text in quotes, or numbers
- Optionally, separator characters like spaces, commas, or hyphens that you want inserted between the combined values
Basic Syntax
=CONCATENATE(text1, [text2], ...)
- text1 is required — your first piece of text or cell reference.
- text2, text3, and so on are optional additional pieces you want joined together, up to 255 arguments in modern Excel versions.
Step-by-Step Instructions
- Click the cell where you want the combined text to appear.
- Type
=CONCATENATE(. - Select or type your first piece of text.
- Add a comma, then add your next piece of text — remember to include any spaces or punctuation you want between them, typed in quotes.
- Repeat for as many pieces as you need.
- Close the parenthesis and press Enter.
Example:
=CONCATENATE(A2, " ", B2)
This combines the first name in A2 and last name in B2 with a space between them.
Practical Examples
Example 1: Combining First and Last Names
=CONCATENATE(A2, " ", B2)
If A2 is “John” and B2 is “Smith,” this returns “John Smith.”
Example 2: Building a Full Address
=CONCATENATE(A2, ", ", B2, ", ", C2, " ", D2)
This might combine street, city, state, and zip code into one formatted address string, with commas and spaces inserted exactly where needed.
Example 3: Combining Text With Numbers
=CONCATENATE("Invoice #", A2)
If A2 contains 1045, this returns “Invoice #1045.” Note that CONCATENATE automatically converts numbers to text during the join.
Example 4: Creating Custom IDs
=CONCATENATE(B2, "-", C2, "-", D2)
This might combine a department code, year, and sequence number into something like “HR-2026-001.”
Example 5: Combining Text With Line Breaks
=CONCATENATE(A2, CHAR(10), B2)
This joins two pieces of text with a line break between them instead of a space — useful for multi-line labels, but you’ll need to enable “Wrap Text” on the cell for the line break to display properly.
Example 6: Using the “&” Operator as a Shortcut
=A2&" "&B2
This produces the exact same result as CONCATENATE(A2, ” “, B2) but with a shorter syntax. Many experienced Excel users prefer the “&” operator for quick joins.
Common Mistakes to Avoid
- Forgetting spaces or separators between values — A very common mistake is writing
=CONCATENATE(A2,B2)and ending up with “JohnSmith” instead of “John Smith” because no space was included between the arguments. - Not putting typed text in quotation marks — Any literal text you want included, like a space, hyphen, or label, must be wrapped in quotes; leaving them out causes a formula error.
- Assuming CONCATENATE will format numbers automatically — If you combine a date or currency value using CONCATENATE, it converts the underlying serial number to plain text rather than preserving formatting, so a date might display as a large number instead of a readable date unless you wrap it in the TEXT function first.
- Relying on CONCATENATE for very large text combinations without checking limits — While it supports up to 255 arguments, extremely long combined strings can sometimes behave unpredictably in certain workbook configurations, so testing your results is wise.
- Not converting formulas to values when needed — If you copy and paste a CONCATENATE result elsewhere, remember it’s still a formula referencing the original cells; use Paste Special > Values if you need a static, unlinked copy of the combined text.
Troubleshooting Tips
- Numbers or dates look wrong in the combined text: Wrap the specific reference in the TEXT function to control its format, like
=CONCATENATE(A2, " - ", TEXT(B2, "MM/DD/YYYY")). - Formula returns an error instead of combined text: Check for missing commas between arguments or unmatched quotation marks around literal text.
- Combined result has no space where expected: Double-check that you included ” ” (a space in quotes) as its own argument between the values you’re joining.
- CONCATENATE seems to be deprecated or flagged: Newer versions of Excel suggest CONCAT as the modern replacement, but CONCATENATE is still fully functional and hasn’t actually been removed from any current version.
Real-World Use Cases
- HR teams combining first and last names into a single full-name field for reports or ID badges.
- E-commerce businesses building complete product codes from category, size, and color fields.
- Marketing teams creating personalized email subject lines by combining customer names with campaign text.
- Mail merge preparation for creating labels, letters, and certificates from separate data fields.
- Finance departments generating invoice numbers or reference codes from multiple components.
- Data cleaning projects reconstructing addresses or contact information that was originally split across many columns.
Best Practices
- Use the “&” operator for quick, simple joins, but stick with CONCATENATE (or CONCAT) for longer formulas where the function name makes the logic clearer to someone reading it later.
- Always account for spacing and punctuation explicitly — Excel will never insert a space automatically between joined values.
- Wrap numbers, dates, and currency values in the TEXT function before combining them if you need specific formatting preserved.
- When building IDs or codes, keep your separator characters consistent throughout your dataset for easier searching and sorting later.
- Consider using TEXTJOIN instead of CONCATENATE when combining a large range of cells with a common separator, since TEXTJOIN handles ranges more efficiently and lets you skip blank cells automatically.
Frequently Asked Questions
Is CONCATENATE being removed from Excel? No, it’s still fully functional in every current version. Microsoft has simply marked it as a “legacy” function and recommends CONCAT or TEXTJOIN for new work, but CONCATENATE isn’t going away and remains widely taught and used.
What’s the difference between CONCATENATE and TEXTJOIN? CONCATENATE joins individual pieces of text one at a time with no built-in separator or way to skip blanks. TEXTJOIN lets you specify a separator once and apply it across an entire range, while also giving you the option to ignore empty cells automatically — much more efficient for combining long lists.
Can CONCATENATE combine more than two cells at once? Yes, it supports up to 255 separate arguments, so you can join dozens of cells and text fragments together in a single formula, as long as you remember to add separators between each one.
Why does my CONCATENATE result show a number instead of a formatted date? CONCATENATE converts everything into plain text, stripping away any special number formatting like date or currency styles. Wrap the specific cell reference in the TEXT function first, such as TEXT(A2, "MM/DD/YYYY"), to preserve the format you want in the final combined text.
Can I use CONCATENATE to add a line break between two values? Yes, insert CHAR(10) between your two pieces of text, and make sure the cell has “Wrap Text” enabled under the Home tab, otherwise the line break won’t display visually even though it’s technically part of the text.
Final Thoughts
CONCATENATE might be an older function, but it remains one of the most practical tools for anyone who regularly needs to merge text from multiple sources into a single, clean output. Whether you’re preparing a mailing list, generating custom IDs, or cleaning up messy imported data, understanding how to combine values correctly — spaces, punctuation, and formatting included — will save you an enormous amount of manual retyping over time.
