The CONCAT, CONCATENATE and TEXTJOIN functions are popular as text combining functions, which have been designed to combine a range of strings that contain parts of text, phrases or numbers.

From which CONCATENATE is the widely used one as it was available in the earlier versions of Office. The CONCAT and TEXTJOIN functions have recently been introduced and are only available in the latest version of Office (EXCEL 2016, Excel Online and latest mobile excel versions).

 

CONCATENATE Function

 

The CONCATENATE is used to join text from several cells.

The syntax is explained below:

 

CONCATENATE(text1, [text2],…)

 

text1:                                    Specify the first item to be joined (Required)

[text2]:                                 Additional items to be joined. (Optional argument)

 

You can enter up to 255 arguments.

CONCAT Function

 

This function provides the same functionality of CONCATENATE, and which has enabled to reduce the length of function name and its arguments. Though CONCAT replaces the CONCATENATE, it continues to be available in latest Office/Excel to provide compatibility with the previous versions.

The syntax is explained below:

 

CONCAT(text1, [text2],…)

 

text1:                                    Specify the text entry or range of cells to be joined (Required)

[text2]:                                 This allows you to specify further ranges to combine texts, if the range is not continuous. (Optional argument)

 

TEXTJOIN Function

 

The TEXTJOIN function is also a text combining function available in Excel 2016. This can be easily used to include a delimiter between text strings and this delimiter can even be an empty string. This function is also capable of ignoring empty cells in a given range.

The syntax is explained below:

 

 

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

 

delimiter:                           This is the separating character between the text strings. A comma, space or any other character can be specified as the delimiter. It needs to be enclosed in double quotes or can also be referenced to a valid cell content. (Required)

ignore_empty:                 If this argument is set to TRUE, then the empty cells will be ignored. If it is False, then empty cells will be included. (Required)

text1:                                    Specify the range of cells to be joined, no need to enter each cell one by one. (Required)

[text2]:                                 This allows you to specify further ranges to combine texts, if the range is not continuous. (Optional argument)

 

Example:

• Using CONCATENATE

 

Note that A3, B3 and C3 cells have been combined with a comma and space in between the cells.

 

• Using CONCAT

 

 

When combining text with delimiters, both CONCAT and CONCATENATE would look as the same.

But when you do not need delimiters, CONCAT would be much compact as you can select a range of cells. It is necessary to specify each cell individually for CONCATENATE. See the below example:

 

 

• Using TEXTJOIN

 

This would be the ideal choice for combining text with large data sets.

 

 

The advantages of this is you can simply specify a delimiter, ignore empty cells (so there won’t be unnecessary spaces) and handle large data ranges.