How to Use CONCATENATE in Excel

Key Insights

CONCATENATE remains widely used despite being superseded by CONCAT and TEXTJOIN—understanding all three gives you flexibility across different Excel versions and use cases
The most common mistake is forgetting to include spaces or delimiters as separate arguments, resulting in text that runs together without proper formatting
Combining CONCATENATE with TEXT(), IF(), and other functions unlocks powerful data formatting capabilities that go far beyond simple text joining

Understanding CONCATENATE in Excel

CONCATENATE is Excel’s original function for joining multiple text strings into a single cell. Despite Microsoft introducing newer alternatives like CONCAT (2016) and TEXTJOIN (2019), CONCATENATE remains prevalent in existing spreadsheets and is still fully supported in all Excel versions.

The function does exactly what its name suggests: it concatenates (links together) separate pieces of text into one continuous string. This becomes essential when you’re working with data split across multiple columns—names in separate first/last columns, addresses broken into components, or any scenario where you need to combine information for reports, exports, or further processing.

Why learn CONCATENATE when newer options exist? First, you’ll encounter it constantly in legacy spreadsheets. Second, it’s available in every Excel version, making your formulas more portable. Third, understanding CONCATENATE makes the transition to modern alternatives intuitive since they build on the same core concept.

Basic CONCATENATE Syntax

The CONCATENATE function follows a straightforward structure:

=CONCATENATE(text1, [text2], [text3], ...)

Each argument can be a text string (in quotes), a cell reference, a number, or the result of another function. The function processes arguments left to right, joining them in the exact order you specify.

Here’s the simplest practical example—combining a first and last name:

=CONCATENATE(A2, " ", B2)

If cell A2 contains “John” and B2 contains “Smith”, this formula returns “John Smith”. Notice the second argument: " ". This is a text string containing a single space. Without it, you’d get “JohnSmith”—a common beginner mistake.

You can extend this to include middle names, titles, or suffixes:

=CONCATENATE("Dr. ", A2, " ", B2, ", ", C2)

With A2=“Sarah”, B2=“Johnson”, C2=“PhD”, this produces “Dr. Sarah Johnson, PhD”.

Practical Use Cases

CONCATENATE shines in real-world data management scenarios. Let’s explore several common applications.

Building Complete Addresses

When address data arrives in separate columns (street, city, state, ZIP), you need to combine them for mailing labels or reports:

=CONCATENATE(A2, " ", B2, ", ", C2, " ", D2)

If A2=“123 Main St”, B2=“Springfield”, C2=“IL”, D2=“62701”, this creates “123 Main St Springfield, IL 62701”. For better formatting, add a line break using CHAR(10):

=CONCATENATE(A2, CHAR(10), B2, ", ", C2, " ", D2)

Remember to enable “Wrap Text” in the cell format to display the line break properly.

Generating Email Addresses or Usernames

Many organizations follow standardized email formats. You can automate email address creation:

=CONCATENATE(LOWER(A2), ".", LOWER(B2), "@company.com")

With A2=“John” and B2=“Smith”, this generates “john.smith@company.com”. The LOWER() function ensures consistent formatting regardless of how names are entered.

Formatting Phone Numbers

When phone numbers are stored as separate area code, prefix, and line number:

=CONCATENATE("(", A2, ") ", B2, "-", C2)

This converts 555, 123, 4567 into “(555) 123-4567”.

Creating Custom ID Codes

Combine department codes, dates, and sequential numbers for tracking:

=CONCATENATE("INV-", TEXT(A2, "YYYYMMDD"), "-", B2)

If A2 contains a date (1/15/2024) and B2 contains 1001, this produces “INV-20240115-1001”.

Combining CONCATENATE with Other Functions

CONCATENATE’s real power emerges when you nest it with other Excel functions.

Formatting Dates and Numbers with TEXT()

Excel stores dates as numbers, which display incorrectly when concatenated directly. The TEXT() function solves this:

=CONCATENATE("Invoice Date: ", TEXT(A2, "mm/dd/yyyy"))

If A2 contains the date 1/15/2024, this returns “Invoice Date: 01/15/2024” instead of “Invoice Date: 45306” (the underlying serial number).

For currency formatting:

=CONCATENATE("Total Amount: ", TEXT(B2, "$#,##0.00"))

With B2=1234.5, this produces “Total Amount: $1,234.50”.

Conditional Concatenation with IF()

Sometimes you only want to include certain text when conditions are met:

=CONCATENATE(A2, " ", B2, IF(C2<>"", ", " & C2, ""))

This adds the suffix from C2 only if it’s not empty, preventing awkward trailing commas.

For more complex logic:

=CONCATENATE("Status: ", IF(A2>100, "High", IF(A2>50, "Medium", "Low")))

Text Case Formatting

Ensure consistent capitalization by combining CONCATENATE with UPPER(), LOWER(), or PROPER():

=CONCATENATE(UPPER(A2), " - ", PROPER(B2))

This converts A2 to all caps and B2 to title case before joining them.

Common Mistakes and Troubleshooting

Missing Spaces

The number one error is forgetting to include spaces between concatenated values:

=CONCATENATE(A2, B2)  // Wrong: produces "JohnSmith"
=CONCATENATE(A2, " ", B2)  // Correct: produces "John Smith"

Always include explicit space characters as separate arguments or within your text strings.

The 255 Argument Limit

In Excel 2013 and earlier, CONCATENATE accepts a maximum of 255 arguments. If you’re combining dozens of cells, you’ll hit this limit. The workaround is nesting multiple CONCATENATE functions:

=CONCATENATE(CONCATENATE(A1, A2, A3), CONCATENATE(B1, B2, B3))

Better yet, upgrade to CONCAT or TEXTJOIN if available.

Empty Cell Behavior

CONCATENATE treats empty cells as blank strings, not errors. This means:

=CONCATENATE(A2, " ", B2)

If B2 is empty, you get “John " with a trailing space. This might cause issues in lookups or comparisons. Address this with IF():

=CONCATENATE(A2, IF(B2<>"", " " & B2, ""))

Text vs. Numbers

When concatenating numbers, Excel converts them to text. This loses formatting but prevents calculation errors. If you need specific number formats, always use TEXT().

Modern Alternatives: CONCAT and TEXTJOIN

While CONCATENATE works perfectly well, newer functions offer advantages worth considering.

CONCAT Function

Introduced in Excel 2016, CONCAT simplifies syntax and accepts ranges:

// CONCATENATE version
=CONCATENATE(A2, " ", B2, " ", C2)

// CONCAT version
=CONCAT(A2, " ", B2, " ", C2)

The real advantage is range support:

=CONCAT(A2:E2)  // Joins all cells in the range

However, CONCAT doesn’t let you specify delimiters between items, so it’s less useful for comma-separated lists.

TEXTJOIN Function

TEXTJOIN (Excel 2019/Office 365) is the most powerful option:

=TEXTJOIN(", ", TRUE, A2:E2)

This joins all cells in A2:E2 with “, " as the delimiter and automatically ignores empty cells (the TRUE parameter). Compare this to the CONCATENATE equivalent:

=CONCATENATE(A2, ", ", B2, ", ", C2, ", ", D2, ", ", E2)

TEXTJOIN is dramatically cleaner for combining multiple values with delimiters.

Migration Strategy

When updating old spreadsheets, search for CONCATENATE and evaluate each use:

Simple two-cell joins: Keep CONCATENATE or switch to the & operator
Multiple cells with same delimiter: Replace with TEXTJOIN
Multiple cells without delimiters: Replace with CONCAT
Complex nested formulas: Consider TEXTJOIN with IF() for conditional inclusion

The & operator offers the most concise syntax for simple cases:

=A2 & " " & B2  // Equivalent to =CONCATENATE(A2, " ", B2)

Conclusion

CONCATENATE remains a fundamental Excel skill despite the availability of newer functions. Master the basics—especially remembering to include spaces and delimiters—then explore combinations with TEXT(), IF(), and formatting functions for advanced applications. When working in modern Excel versions, evaluate whether TEXTJOIN or CONCAT might simplify your formulas, but don’t hesitate to use CONCATENATE when it’s the clearest solution for your needs.