Function Reference: CONCAT, CONCATENATE
| Syntax | ||
| =CONCAT(text1,[text2]...[text255]) =CONCATENATE(text1,[text2]...[text255]) |
||
| Purpose / Description | ||
| Returns a text string in which each of the provided text strings have been concatenated together - joined in order. | ||
| Arguments | ||
| Name | Type | Description |
| text1 | text string | A constant, reference or nested expression that returns the first string to be concatenated. For the CONCAT function only, each argument may be a reference to more than one cell, and the references may span worksheets. |
| [text2]...[text255] | text string | Up to 254 optional constant, references or nested expressions that return more text strings to be concatenated. For the CONCAT function only, each argument may be a reference to more than one cell, and the references may span worksheets. |
| Return Values | ||
| Type | Specific Value | Description |
| text string | Returns a text string in which all the provided values are concatenated. Numbers and logical s are coerced to strings. For CONCAT, which accepts multi-cell references, multi-cell ranges are added in order, by worksheet, by column by row. If the result of concatenating the text strings provided requires a result string longer than 32,767 characters, CONCATENATE truncates the result to that limit, CONCAT generates an error, see below. | |
| error | #CALC! | CONCAT returns this value if the resulting text string, including delimiters would exceed the limit for a text string in a worksheet cell of 32,767 characters. |
| error | Any errors referenced by arguments are returned due to propagation of error values. | |
| Notes / Comments / Advice / Warnings | ||
| Note: | Number and logical values are coerced to text strings, and will appear in the format they would have under the General number format. | |
| Comment: | CONCATENATE is officially deprecated and really only provided for legacy workbook support. Frankly, we never saw any use in the functions, as everything it could do was more simply done with a concatenation operator (&). | |
| Comment: | CONCAT, with the ability to accept multi-cell and even across sheet references represented a significant improvement on CONCATENATE. | |
| Advice: | We would generally recommend TEXTJOIN instead of either of these functions, as it provides the option to designate a delimiter in the concatenated string, and also whether blank cells should be skipped in generating the output. | |
| Examples: | Open / download sample workbook: TEXTJOIN, CONCAT, CONCATENATE.xlsx | |