One of the most important operations in Excel formulas is concatenation.
In Excel formulas, concatenation is the process of joining one value to another to form atext string.
The values being joined can be hardcoded text, cell references, or results from other formulas.
Then I’ll introduce the three Excel functions dedicated to concatenation: CONCATENATE, CONCAT, and TEXTJOIN.
These functions can make sense when you oughta concatenate many values at the same time.
As with so many things in Excel, the most important thing is to understand the basics first.
What is concatenation?
Concatenation is the operation of joining values together to form text.
It’s important to understand that the result from concatenation is always text, even when concatenation involves numbers.
For example, while 100 is a numeric value, “100” is a text value.
Now let’s extend the text message above to add a period (.)
This example shows the basics of concatenation in Excel with the ampersand (&) operator.
Now let’s look at an example of concatenation with some basic formula logic to customize a message.
Now let’s extend the formula with some conditional logic.
Suppose we want to add the text “Nice work!”
to the end of the message when the score is 85 or greater.
Otherwise, IF returns an empty string ("").
so that this text doesn’t run into the period*.
Concatenation with number formatting
One tricky aspect of concatenation in Excel isnumber formatting.
This means that number formatting will be lost when you concatenate a formatted number.
For example, in the worksheet below, B5 contains 99 formatted with the Currency number format.
As a result, the number 99 is displayed as $99.00 in the result.
For more details, seeExcel custom number formats.
CONCATENATE function
TheCONCATENATE functionis an older function now replaced by the CONCAT function.
CONCATENATE allows you to perform simple concatenation only.
CONCAT function
TheCONCAT functionreplaces the CONCATENATE function in newer versions of Excel.
The ability to provide a range is the primary advantage of CONCAT over CONCATENATE.
Although CONCAT can handle a range, there is no way to provide a delimiter as a separate argument.
For this, we need to use the TEXTJOIN function.
TEXTJOIN function
Finally, there is theTEXTJOIN function.
Like the CONCAT function, TEXTJOIN is able to accept arangeorarrayof values to concatenate.
The second argument,ignore_empty, is a Boolean that indicates whether TEXTJOIN should ignore or process empty values.
The remaining arguments,text1,text2, etc.
represent the values to be joined.
The next four formulas all supply one or more characters fordelimiter.
In F10,ignore_emptyhas been set to TRUE, and TEXTJOIN ignores the empty value in cell C10.
