How to Use CONCAT in Excel
CONCAT is Excel's modern text-combining function that merges values from multiple cells or ranges into a single text string. Microsoft introduced it in 2016 to replace the older CONCATENATE function,...
Key Insights
- CONCAT replaces the legacy CONCATENATE function and can accept both individual cell references and entire ranges, making it more flexible for combining text from multiple sources
- Unlike TEXTJOIN, CONCAT doesn’t include built-in delimiter support, but it excels at simple text merging and works seamlessly with arrays in modern Excel versions
- CONCAT is only available in Excel 2016 and later (Office 365, Excel 2019, 2021), so organizations using older versions must stick with CONCATENATE or the ampersand operator
Understanding the CONCAT Function
CONCAT is Excel’s modern text-combining function that merges values from multiple cells or ranges into a single text string. Microsoft introduced it in 2016 to replace the older CONCATENATE function, addressing several limitations of its predecessor while maintaining backward compatibility through simpler syntax.
The primary advantage of CONCAT is its ability to accept ranges as arguments. While CONCATENATE required you to reference each cell individually (which became tedious with many cells), CONCAT lets you select an entire range and automatically combines all values. This makes it significantly more efficient when working with dynamic datasets or lengthy text combinations.
You’ll use CONCAT when you need to merge text without delimiters between values. If you need separators like commas, spaces, or custom characters between each item, TEXTJOIN is typically the better choice. However, CONCAT works perfectly when combined with manual delimiters or when concatenating a small number of known cells.
Basic CONCAT Syntax
The CONCAT function follows a straightforward syntax:
=CONCAT(text1, [text2], [text3], ...)
Each argument can be a text string, number, cell reference, or range. Excel evaluates up to 255 arguments, giving you substantial flexibility.
Here’s a simple example combining first and last names:
=CONCAT(A2, B2)
If cell A2 contains “Sarah” and B2 contains “Johnson”, this formula returns “SarahJohnson” without any space. To add proper spacing:
=CONCAT(A2, " ", B2)
This produces “Sarah Johnson” by inserting a space character between the two names.
You can also add text strings directly into the formula for labels or formatting:
=CONCAT("Employee: ", A2, " ", B2)
Result: “Employee: Sarah Johnson”
Here’s a practical example for creating email addresses from username and domain components:
=CONCAT(A2, "@", B2, ".com")
If A2 is “sjohnson” and B2 is “company”, you get “sjohnson@company.com”.
Working with Cell Ranges
CONCAT’s ability to handle ranges sets it apart from its predecessor. You can reference an entire column, row, or rectangular range:
=CONCAT(A1:A5)
This combines all values in cells A1 through A5 into one continuous string. If those cells contain “Apple”, “Banana”, “Cherry”, “Date”, and “Elderberry”, the result is “AppleBananaCherryDateElderberry”.
The function processes ranges left-to-right, top-to-bottom. For a rectangular range:
=CONCAT(A1:B3)
Excel reads A1, B1, A2, B2, A3, B3 in that order, concatenating each value sequentially.
Here’s a real-world example combining product codes from a range:
=CONCAT("Product IDs: ", A2:A10)
If your range contains order items, this creates a single string with all product IDs concatenated together. Note that without delimiters, the values run together, which may not be ideal for readability. For better formatting, you might combine CONCAT with other techniques:
=CONCAT("SKUs: ", A2, ", ", A3, ", ", A4)
Though for this specific use case, TEXTJOIN would be more efficient.
Practical Use Cases and Advanced Techniques
CONCAT becomes powerful when combined with other Excel functions. Here are several practical applications:
Building Full Addresses:
=CONCAT(A2, " ", B2, ", ", C2, ", ", D2, " ", E2)
Where A2=street address, B2=unit number, C2=city, D2=state, E2=ZIP code. This might produce: “123 Main St Apt 4B, Boston, MA 02108”
Formatting Phone Numbers:
=CONCAT("(", A2, ") ", B2, "-", C2)
Converts separate area code (A2), prefix (B2), and line number (C2) into “(555) 123-4567” format.
Creating URLs with Parameters:
=CONCAT("https://example.com/user/", A2, "?ref=", B2)
Dynamically generates URLs based on user IDs and referral codes.
Combining with TEXT for Number Formatting:
=CONCAT("Total: $", TEXT(A2, "#,##0.00"))
If A2 contains 1234.5, this returns “Total: $1,234.50” with proper currency formatting.
Using UPPER/LOWER for Consistent Casing:
=CONCAT(UPPER(A2), " ", LOWER(B2))
Ensures the first name is uppercase and last name is lowercase, regardless of source data formatting.
Removing Extra Spaces with TRIM:
=CONCAT(TRIM(A2), " ", TRIM(B2))
Eliminates leading, trailing, and excess internal spaces before concatenation, ensuring clean output.
CONCAT vs. Other Concatenation Methods
Excel offers several ways to combine text, each with specific advantages:
CONCAT vs. CONCATENATE: CONCATENATE is the legacy function that CONCAT replaces. The key difference: CONCATENATE doesn’t accept ranges, only individual cell references. While CONCATENATE still works, Microsoft recommends CONCAT for new workbooks.
=CONCATENATE(A1, A2, A3, A4, A5) // Old method
=CONCAT(A1:A5) // New method - same result
CONCAT vs. Ampersand (&): The ampersand operator provides the most concise syntax for simple concatenations:
=A1 & " " & B1 // Using &
=CONCAT(A1, " ", B1) // Using CONCAT
Both produce identical results. The ampersand is often faster to type for simple combinations, but CONCAT is clearer in complex formulas and handles ranges, which the ampersand cannot.
CONCAT vs. TEXTJOIN: TEXTJOIN is the superior choice when you need delimiters between values:
=TEXTJOIN(", ", TRUE, A1:A5) // Adds commas automatically
=CONCAT(A1, ", ", A2, ", ", A3, ", ", A4, ", ", A5) // Manual delimiters
TEXTJOIN also has a built-in option to ignore empty cells (the TRUE parameter above), while CONCAT includes them. Use CONCAT for simple merging without delimiters, TEXTJOIN for creating lists with separators.
Performance Considerations: For small datasets, performance differences are negligible. In workbooks with thousands of concatenation formulas, the ampersand operator is marginally faster, but CONCAT offers better readability and maintainability, which typically outweighs minor performance differences.
Common Errors and Troubleshooting
#NAME? Error: This error appears when using CONCAT in Excel 2013 or earlier versions that don’t support the function. Solution: Use CONCATENATE or the ampersand operator instead, or upgrade to Excel 2016 or later.
Character Limit: Excel limits text strings to 32,767 characters. If your CONCAT result exceeds this, Excel truncates the output. For extremely large concatenations, consider breaking the operation into multiple cells or using VBA.
Empty Cell Handling: CONCAT treats empty cells as empty strings (not errors), which is usually desired behavior. However, this can create formatting issues:
=CONCAT(A1, " ", B1)
If B1 is empty, you get “Sarah " with a trailing space. To handle this:
=CONCAT(A1, IF(B1<>"", " " & B1, ""))
This adds the space and B1 only if B1 contains data.
Error Handling with IFERROR: When concatenating cells that might contain errors:
=CONCAT(IFERROR(A1, ""), " ", IFERROR(B1, ""))
This replaces any errors in source cells with empty strings, preventing error propagation.
Unexpected Results with Numbers: CONCAT converts numbers to text using default formatting, which may not match your expectations:
=CONCAT("Date: ", A1) // If A1 contains 1/1/2024
This might return “Date: 45292” (the serial number). Use TEXT to control formatting:
=CONCAT("Date: ", TEXT(A1, "mm/dd/yyyy"))
Range Reference Issues: When copying formulas with range references, ensure your ranges adjust correctly. Use absolute references ($A$1:$A$5) when you want the range to remain fixed, or mixed references (A$1:A$5) for partial locking.
CONCAT is a straightforward but essential function for modern Excel users. Master it alongside TEXTJOIN and the ampersand operator, and you’ll have complete control over text manipulation in your spreadsheets. The key is choosing the right tool for each situation: CONCAT for simple merging, TEXTJOIN for delimited lists, and the ampersand for quick, simple combinations.