Comparison of concatenating functions
Now you know different methods to combine text strings in Excel 👏🏻
Let’s compare them.
CONCAT vs Ampersand operator
Say you have to combine data from a range of cells.
Then, you can easily use CONCAT as it can select the entire cell range as one argument.
But, if you are using an ampersand, you have to select all cell references separately.
With ampersand, we cannot give cell ranges.
However, the CONCAT has the following limits.
- We can give a maximum of 253 text arguments
- The desired result must have a maximum of 32,767 characters
The above limitations do not apply when combining text strings with ampersand operators.
CONCAT vs TEXTJOIN
CONCAT and TEXTJOIN both use ranges.
But, the TEXTJOIN function has 2 main advantages over the CONCAT.
- We can specify a delimiter to be entered between cell references.
- We can set the function to ignore empty values.
So, the TEXTJOIN function is more flexible than CONCAT 👌
However, the TEXTJOIN function is currently available for Microsoft Excel 2019 and newer versions only.
CONCAT vs CONCATENATE
The function CONCAT is available from Excel 2019 version onwards. But the CONCATENATE function is available from Excel 2007 onwards.
In the CONCATENATE function, we cannot use a range of values as a text argument and get into one cell.
Then concatenated range will spill into multiple columns.
So, in CONCATENATE, text1, text2 and other text arguments must specify individually.
However, using CONCAT, we can give a cell range as a text argument.