In Excel 2010 or later, certain functions are meant to be replaced with new functions to provide better results and names which (theoretically) make more sense. For example, the RANK function , which generates a rank for number against a range of numbers is listed as a compatibility function, and is replaced by the RANK.AVG and RANK.EQ functions.
Compatibility functions can still be used in later versions of Excel to ensure backward compatibility. However, Microsoft recommends that users deploy the newer functions (compatibility function replacements) when backward compatibility isn’t required.
Not all new functions with similar names indicate compatibility functions. For example, the NETWORKDAYS function , which calculates workdays between dates, is not listed as a compatibility function even though NETWORKDAYS.INTL is a newer function with the same purpose. The NETWORKDAYS.INTL simply provides more flexibility, allowing users to customize which days of the week are treated as weekends (i.e. non-work days).
Microsoft maintains a list functions with a category for compatibility functions .
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
