Use TEXTJOIN instead of CONCATENATE in Excel

When an Excel spreadsheet has text in separate cells and you want to combine it, you can use TEXTJOIN instead of CONCATENATE in Excel. At least, in the latest versions of MS Excel (2019 and Office 365).

TEXTJOIN is a function that does exactly that, join text together. Excel always had the option to combine the content of cells together using the CONCATENATE function. But concatenate often results in a very long formula.

Another advantage of using the TEXTJOIN function is that there is no need to repeat a separator, you can specify a separator that will be used between the contents of each cell!

Let’s look at an example.

The TEXTJOIN function has 3 parameters. The first parameter specifies the separator to use. In the example, this is a space, since we are creating a sentence from separate words (and word combinations). If you do not want to use a separator, simply provide an empty string ( two quotes, “” ). The separator is not limited to a single character, so a comma and a space would also be fine ( “, ” ).

The second parameter tells the function if empty cells should be ignored. In most cases, you would use the value TRUE for this, because otherwise, the result would just repeat separators…

And the third parameter is the cell range of which the contents need to be joined. This can be a selection of horizontal or vertical cells.

The old way of doing this would generally mean using the CONCATENATE function.

In this example, the formula is still not too long, but imagine what will happen when the number of cells in the selected range increases!

Textjoin for separate selection ranges

It might seem that the CONCATENATE function is more flexible because the cells can exist anywhere on the sheet. That way the CONCATENATE function can handle this example.

Just use the function:

CONCATENTATE(C5," ",C6," ",B2," ",B3)

If we try to use TEXTJOIN, we can select the whole block B2:C6 as the range and specify that empty cells should be ignored. Problem is that the sequence of the sentence will be wrong.

This might seem like a simple example and an obvious issue, but it brings up the optional parameters of the TEXTJOIN function.

Microsoft has left room to specify additional parameters for the TEXTJOIN function. So apart from the third parameter, you can provide additional parameters.

The solution in this example becomes:

TEXTJOIN(" ",TRUE,C5:C6,"B2:B3)

Basically, the TEXTJOIN can still be used in the same way the CONCATENATE function is used. But it has become more powerful with the additional parameters!