Excel TEXTJOIN and CONCAT functions are relatively new, and welcome additions to Excel’s plethora of functions. Both functions have been released with Excel 2016 as a replacement for the CONCATENATE function to make it easier to merge multiple text strings. In this guide, we're going to show you differences between Excel TEXTJOIN and CONCAT functions.
First stop in TEXTJOIN vs CONCAT article is CONCAT function.
Excel CONCAT Function
Microsoft states that the CONCAT function is intended as a replacement for the 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 to be merged individually.
Syntax
Example
The example below demonstrates how CONCAT function can merge all strings in the given reference. You do not need to select each cell individually. On the other hand, supplying a range makes the function merge the strings without any separators. If you need separators, the CONCAT function works the same way as its older version CONCATENATE.
See the first two rows and second two rows below.
Let's continue TEXTJOIN vs CONCAT article with TEXTJOIN function.
Excel TEXTJOIN Function
TEXTJOIN function is the solution for the separator problem! It can join strings with a delimiter. Thanks to the delimiter support, you can add a delimiter string and a logical value to determine whether the function is to ignore empty cells. If the function is set to not ignore empty cells and there is an empty cell in the range, the delimiter string will be repeated in the resulting string.
Syntax
Example
The example below is using the same data from the previous example. You can see that strings in each cell are separated by given delimiter, a space character (" "). Additionally, you can use a special character by using CHAR function or more than one characters as well.
Summary of the Excel TEXTJOIN and CONCAT match-up
- The CONCATENATE function is obsolete!
- Use the CONCAT if you do not need delimiters between strings.
- Prefer using the TEXTJOIN for adding delimiters in the resulting string.
