Concatenation refers to the operation of joining values together to create a text string. In Excel, you can concatenate with the concatenation operator, which is an ampersand (&) or by using one of several functions for concatenation.
In the screen above, the formulas used for concatenation are:
=B6&" and pears"
=B7&" apples"
="The term is "&12&" months"
="Sale ends "&TEXT(B8,"mmmm d")
Note that in the last example, we are using the TEXT function to format the date in B8 as a date in “mmm d” format.
Concatenation with functions
Excel contains three functions you can also use for concatenation: the CONCATENATE function , the CONCAT function , and the TEXTJOIN function . CONCAT and TEXTJOIN are new functions available in Office 365 and Excel 2019.
For a complete overview, see: How to concatenate in Excel
The acronym “CSE” stands for “Control + Shift + Enter”. A CSE formula in Excel is an array formula that must be entered with control + shift + enter. When a formula is entered with CSE, Excel automatically wraps the formula in curly braces {}.
In the example shown, the formula in F5 is:
{=SUM(C5:C14*D5:D14)}
This array formula provides “total sales” in one cell, without a separate column and formula to multiply quantity times price. The curly braces are a clear indication that the formula was entered with CSE. If the same formula is entered without CSE, the result is incorrect.
Not all array formulas require CSE
Not all array formulas need to be entered with CSE. Entering a formula with CSE explicitly disables a behavior in Excel called " implicit intersection “, but some functions do this natively. For example, in the example shown, the SUMPRODUCT function can be used instead of SUM without CSE:
=SUMPRODUCT(C5:C14*D5:D14)
The SUMPRODUCT formula returns the same result but does not require CSE.
