The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function should have been from the beginning.

It allows you to combine text strings from multiple cell ranges and also use delimiting characters if you want. Not only that, you can also use it in array formulas.


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

The similar CONCATENATE function forced you to select each cell in order to join text strings, see picture below. If you had to combine many many cells this function was not option, you had to use a custom vba function.

You can use the TEXTJOIN function with a cell range or multiple cell ranges, this may save you a lot of time if you are working with many cells.


  1. delimiter - A delimiting character or characters
  2. ignore_empty - True lets you ignore empty cells in the third argument, False adds empty cells to the output.
  3. text1 -
  4. [text2]

Example 1

The first argument is the delimiter, the example below shows you the result in cell C4 if you use no delimiting characters. The second argument lets you choose between TRUE or FALSE, if TRUE it will ignore empty cells. The third argument is the cell range.

The following article shows you how to add unique distinct values to a cell using the TEXTJOIN function:

Concatenate unique distinct values

The new TEXTJOIN function in excel 2016 lets you do some amazing things with arrays. This post demonstrates how to […]

Comments(2) Filed in category: Concatenate, Excel, Textjoin

Example 2

This picture shows you the TEXTJOIN function combining multiple cell ranges with a delimiting text string. It also ignores the empty cell A5. You can use up to 252 cell ranges.


TEXTJOIN function and array formulas

Example 3 - Concatenate values based on a condition

It is possible to use this function in an array formula, here is a simple demonstration.


You can see that the third argument contains:


This IF function checks if the numbers in cell range A1:A9 are above 5 and if they are the corresponding value in cell range B1:B9 is returned.

Numbers 6,7,8 and 9 are larger than 5 so corresponding characters F, G, H and I are combined using the delimiting characters "--".

This post demonstrates how to do a lookup and concatenate returning values:

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Comments(249) Filed in category: Concatenate, Excel, Textjoin

TEXTJOIN function vba for previous excel versions

The following user defined function (udf) allows you to combine text strings just like the TEXTJOIN function.


Why would you want to use this udf? TEXTJOIN function is only available in Excel 365, previous excel versions are missing this function.

VBA code

Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)
For Each cellrng In cell_ar
For Each cell In cellrng
If ignore_empty = False Then
result = result & cell & delimiter
If cell <> "" Then
result = result & cell & delimiter
End If
End If
Next cell
Next cellrng
TEXTJOIN = Left(result, Len(result) - Len(delimiter))
End Function

Where do I put this code?

  1. Open VB Editor (Alt + F11)
  2. Click "Insert" on the menu
  3. Click "Module"
  4. Copy code above
  5. Paste it to the code module
  6. Exit VB Editor

How do I use it?

  1. Select a cell
  2. Type Textjoin(
  3. Type your three arguments and an ending parentheses.
  4. Press Enter


delimiter - A text string, if you want nothing type ""

ignore_empty - If TRUE it ignores empty cells

text1, text2, ... - A cell range or multiple cell ranges. Use a comma to separate them.