Excel CONCATENATE function

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.

Related Functions

Excel CONCAT function

The Excel CONCAT function merges multiple text strings into one, perfect for data concatenation without using the ampersand (&).

Excel TEXTJOIN function

The Excel TEXTJOIN function combines text from multiple ranges, including a delimiter, perfect for creating comprehensive strings.

Content Navigation