Excel CONCAT function

Summary

The Excel CONCAT function combines text from multiple ranges and/or strings. It’s an updated version of the CONCATENATE function, providing more flexibility in combining text from different sources.
Syntax
				
					=CONCAT(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

CONCAT is particularly useful for merging text from different cells, ranges, or strings. It’s commonly used in data preparation, reporting, and whenever text from various sources needs to be combined into a single string.

Examples

Simple CONCAT
Combining Text from Different Cells: To concatenate text from individual cells:
				
					=CONCAT(A1, B1)
				
			
This formula combines the text from cells A1 and B1.
CONCAT with Text and Cells
Merging Cells and Literal Text: Concatenating cells with additional text:
				
					=CONCAT("Name: ", A2, ", Age: ", B2)
				
			
This formula combines literal text with the contents of cells A2 and B2.
CONCAT with Ranges
Combining Text from a Range of Cells: Concatenating a range of cells into a single string:
				
					=CONCAT(C1:C5)
				
			
This formula concatenates all text in the range C1 to C5.
CONCAT for Data Organization
Organizing Data into a Single String: To merge data from multiple columns into a formatted string:
				
					=CONCAT(A3, " - ", B3, " - ", C3)
				
			
This combines data from A3, B3, and C3 with hyphens as separators.
CONCAT for Complex Strings
Creating Complex Strings from Multiple Sources: Complex concatenation involving various data types:
				
					=CONCAT("Product: ", D2, ", Quantity: ", E2, ", Price: $", F2)
				
			
This formula creates a detailed string combining text and numbers from cells D2, E2, and F2.

Additional Notes

  • CONCAT can handle both horizontal and vertical ranges.
  • Unlike CONCATENATE, CONCAT allows the use of full column or row references.
  • Useful in scenarios where you need to dynamically combine data without specifying each cell individually.

Related Functions

Excel TEXTJOIN function

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

Excel CONCATENATE function

The Excel CONCATENATE function combines text items into one string, useful for creating labels or combining data (Deprecated in newer versions).

Content Navigation