For a long time, the only option to combine text strings has been using the CONCATENATE function, which does nothing more than what the ampersand (&) operator does. Recently, Microsoft has released the CONCAT and TEXTJOIN formulas with Excel 2016. In this guide, we are going to focus on the CONCAT function and look at some of the differences between the CONCAT vs CONCATENATE functions.
Let's begin with the older formula (CONCATENATE) first and see why you might want to prefer using the CONCAT function instead.
CONCATENATE Function
The CONCATENATE function simply merges its parameters, and returns a string value. However, you can do the exact same thing by using an ampersand (&) character between the strings or texts. CONCATENATE function doesn’t provide any advantage for the most part, unless you need to merge 100 strings and do not want to use the ampersand (&) character 99 times. By the way, you still need to select each cell one-by-one because the function doesn’t support ranges.
Syntax
Example
Below is an example of how you can use the CONCATENATE function. Supply each string as an argument and the function will merge them.
The next part in our CONCAT vs CONCATENATE guide is the upgraded version, the CONCAT function.
CONCAT Function
CONCAT function is one of the relatively newer text merging functions in Excel. Microsoft states that the CONCAT function is intended as a replacement for CONCATENATE function. Although Microsoft Excel continues to support the older function, they suggest that you should use the CONCAT function instead.
The CONCAT function addresses the biggest flaw of the CONCATENATE function, not being able to select a range of strings. Unlike its predecessor, the CONCAT function can merge all strings in a range. This way, it saves you from selecting each cell individually.
Syntax
Example
The example below is using the same data from the previous example. You can see that the formulas here are considerably shorter. However, you still need to add separators manually.
