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.

Download Workbook

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.

Note: This feature is available on Windows or Mac if you have Office 2019, or if you have an Office 365 subscription.

Syntax

CONCAT(text1, [text2, …])

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.

TEXTJOIN vs CONCAT 01

Please see the article that covers the CONCAT function in depth here: CONCAT Function

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

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

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.

You can visit TEXTJOIN function’s own page for more information: TEXTJOIN Function

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.

Reminder: This feature is available on Windows or Mac if you have Office 2019, or if you have an Office 365 subscription