Summary
The Excel CONCATENATE function combines two or more text strings into one string. It’s a straightforward function for merging text from different sources, such as strings, numbers, and cell references.
Syntax
=CONCATENATE(text1, [text2], ...)
- text1, [text2], …: The texts to be combined. Can be strings, cell references, or ranges
Return value
A single string combining all input texts.
How to use
CONCATENATE is commonly used in data preparation, reporting, and text manipulation. It’s ideal for assembling text from various elements into a cohesive format.
Examples
Simple CONCATENATE
Combining Names from Different Cells: To merge first and last names from separate cells:
=CONCATENATE(A1, " ", B1)
If A1 contains “Jane” and B1 contains “Doe”, the formula returns “Jane Doe”.
CONCATENATE with Text and Cells
Merging Cells and Literal Text: Concatenating cells with additional text:
=CONCATENATE("Employee: ", A2, ", Department: ", B2)
Combines “Employee: ” with A2’s content and “, Department: ” with B2’s content.
CONCATENATE for Address Formatting
Formatting Addresses from Different Cells: Concatenating address components into a single string:
=CONCATENATE(C1, ", ", D1, ", ", E1)
This formula combines address elements in C1, D1, and E1, separated by commas.
CONCATENATE in Data Analysis
Combining Data for Analysis and Reporting: Merging various data points into a formatted string for analysis:
=CONCATENATE("Total Sales: ", F2, ", Units Sold: ", G2)
Creates a summary string from sales and units data in F2 and G2.
CONCATENATE for Complex Strings
Assembling Complex Data Strings: Building detailed descriptions from multiple data sources:
=CONCATENATE(H3, " - ", I3, " [", J3, "]")
Combines product, category, and code information from H3, I3, and J3 into a single, formatted string.
Additional Notes
- CONCATENATE is an earlier version of the CONCAT function, which offers similar functionality.
- CONCATENATE does not allow the use of array references or full column/row references, unlike CONCAT.
- Ideal for simple text merging tasks where dynamic range selection is not required.