CONCATENATE Excel function concatenates or joins up to 30 values and returns the result as text. For example, If Cells A1 and B1 contains the values “House” and “Dragon” respectively, then the formula “=CONCATENATE(A1,B1)” joins the cells A1 and B1 returning the output as “HouseDragon”. If you want to have a space between the two, the formula should be =CONCATENATE(A1,” “,B1). For Excel 2019 and later, the CONCAT and TEXTJOIN functions are more flexible alternatives. Here we shall look into the Concatenate Excel function, Formula, its syntax, the different arguments, Errors and other details.
Concatenate Excel Function
The Excel CONCATENATE function concatenates or joins up to 30 values together and returns the result as text. It accepts multiple arguments (maximum 30 values) called text1, text2, text3, etc. These Values may be supplied as cell references, and hard-coded text strings, and the values are concatenated in the order they appear.
Concatenate Formula Syntax
=CONCATENATE (text1, text2, [text3], …)
Arguments
text1 – The first text value to join together.
text2 – The second text value to join together.
text3 – [optional] The third text value to join together.
Output or Result of Concatenate Function
All valid arguments passed to it returned as a concatenated text.
Number Formatting will be Lost
When numeric values (dates, times, percentages, etc.) are passed as arguments to Concatenate function, their number formatting will be lost.
#NAME Error in Excel Concatenate Function
When a text is provided as an argument to concatenate function, such text should always be enclosed in double quotes. When the text is not enclosed in double quotes, Concatenate function returns a #NAME Error.
CONCATENATE Function will not handle ranges
=CONCATENATE(A1:Z1) // Will not work
To concatenate values in ranges, see the CONCAT function. To concatenate many values with a common delimiter, see the TEXTJOIN function. TEXTJOIN can do everything CONCAT can do, but can also accept a delimiter and optionally ignore empty values.
Summary
- CONCATENATE function concatenates or joins up to 30 values together and returns the result as text.
- Its arguments can be text strings, numbers, or cell references that refer to one cell.
- Number formatting of Numeric values will be lost when passed as arguments to concatenate excel function.
- The ampersand character (&) is an alternative to CONCATENATE. The result is the same.
- CONCATENATE Function will not handle ranges