## TEXTJOIN function

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 an array formula.

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.

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.

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

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 "--".

### 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 Else 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?**

- Open VB Editor (Alt + F11)
- Click "Insert" on the menu
- Click "Module"
- Copy code above
- Paste it to the code module
- Exit VB Editor

**How do I use it?**

- Select a cell
- Type Textjoin(
- Type your three arguments and an ending parentheses.
- Press Enter

**Arguments**

*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.

### Combinemerge

Question: How do I merge two ranges into one list? Answer: Excel 2007 array formula in C2: =IFERROR(INDEX(List1, ROWS(C1:$C$1)), IFERROR(INDEX(List2, ROWS(C1:$C$1)-ROWS(List1)), "")) + ENTER. Copy cell C2 and paste it down as far as needed. This example merges two columns […]

Question: Problem description (simplified of course): I have a list of employees (by ID number) and date (by yr & mon) of when they were assigned a certain duty (task). This is in a Work book, on a TAB. Each […]

Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my "List 1" is auto updated and the number of entries in this list will fluctuate. Since the number of entries […]

### Functions

Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from the list A1:A11 in C1. How do I find those summed numbers in C1? I am going to use Excel […]

In this post, I will provide a formula to sum values in column (Qty) where an column (Date) meets two date criteria and an additional criterion in an adjacent column (Product). I have colored the cells in column Qty that […]

Question: Hi, What type of formula could be used if you weren't using a date range and your data was not concatenated? ie: Input Value 1.78 should return a Value of B as it is between the values in Range1 […]

### Textjoin

The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. However if your excel version is missing the TEXTJOIN function you can use a User Defined Function, I have all […]

Joining many text strings in excel is not easy. The Concatenate function accepts only a single cell reference in each argument. Example, =Concatenate(A1, A2, A3) + ENTER To quickly select cells you can press CTRL and click on cells you […]

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 […]

### User defined functions udf

The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. However if your excel version is missing the TEXTJOIN function you can use a User Defined Function, I have all […]

You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website. I would choose the advanced filter […]

Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What is a combination? To explain combinations I must explain the difference between combinations and permutations. Think of permutations as if the order is […]

### Vba

You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website. I would choose the advanced filter […]

In a previous post: How to create a dynamic pivot table and refresh automatically in excel I demonstrated how to refresh a pivot table when a sheet is activated. This post describes how to refresh a pivot table when data is […]

Today I´ll show you how to search all excel workbooks (xls, xlsx, xlsm) in a folder for a text string. The macro creates a new sheet containing the search result. Each result contains a link to a cell where the […]

### 2 Responses to “TEXTJOIN function”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[…] versions to excel 2016, however if you have excel 2016 you can now finally use the much easier TEXTJOIN function. Here is a larger range, A1:A50 with some […]