# How to use the TEXTJOIN function

The TEXTJOIN function is a relatively new function introduced in Excel 2019, 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.

**What's on this page**

- TEXTJOIN Function Syntax
- TEXTJOIN Function Arguments
- How to join cell values without a delimiting character
- How to concatenate cell values ignoring empty cells
- Concatenate values based on a condition
- User Defined Function alternative for the TEXTJOIN function (VBA)
- TEXTJOIN function - how to add row delimiting characters
- Get the Excel File here

## 1. TEXTJOIN Function Syntax

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

## 2. TEXTJOIN Function Arguments

delimiter |
Required. The delimiting character or characters you want to use. |

ignore_empty |
Required. True lets you ignore empty cells in the third argument, False adds empty cells to the output. |

text1 |
Required. The cell range you want to concatenate. |

[text2] |
Optional. Up to 254 additional cell ranges. |

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

## 3. How to join cell values without a delimiting character

The first argument in the TEXTJOIN function is the delimiter, the example above 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

This article demonstrates Excel formulas that extract unique distinct values from a cell range and concatenates the values using any […]

Concatenate unique distinct values

## 4. How to concatenate cell values ignoring empty cells

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.

## 5. How to concatenate values based on a condition

It is possible to use the TEXTJOIN 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 "--".

### 5.1 Explaining formula

#### Step 1 - Logical expression

The larger than character is a logical operator that lets you check if a number is larger than another number, the result is a boolean value TRUE or FALSE.

A1:A9>5

becomes

{1; 2; 3; 4; 5; 6; 7; 8; 9}>5

and returns

{FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE}.

#### Step 2 - Evaluate IF function

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(*logical_test, [value_if_true], [value_if_false]*)

IF(A1:A9>5,B1:B9,"")

becomes

IF({FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE},{"A"; "B"; "C"; "D"; "E"; "F"; "G"; "H"; "I"},"")

and returns

{""; ""; ""; ""; ""; "F"; "G"; "H"; "I"}.

#### Step 3 - Join values

TEXTJOIN("--", TRUE, IF(A1:A9>5, B1:B9, ""))

becomes

TEXTJOIN("--", TRUE, {""; ""; ""; ""; ""; "F"; "G"; "H"; "I"})

and returns

"F--G--H--I".

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

Lookup and return multiple values concatenated into one cell

## 6. 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.

**6.1 VBA code**

'Name User Defined Function, specify parameters and declare data types Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant) 'For each statement, iterate through each cell range For Each cellrng In cell_ar 'For each statement, iterate through each cell in cell range For Each cell In cellrng 'If ... Then ... Else ... End If statement 'Check if parameter ignore_empty is equal to boolean value False If ignore_empty = False Then 'Concatenate cell value and delimiter with variable result and save to result result = result &amp;amp; cell &amp;amp; delimiter 'Go here if ignore_empty is not equal to False Else 'Check if cell value is not equal to nothing If cell &amp;lt;&amp;gt; "" Then 'Concatenate cell value and delimiter with variable result and save to result result = result &amp;amp; cell &amp;amp; delimiter End If End If 'Continue with next cell value Next cell 'Continue with next cell range Next cellrng 'Remove last delimiter in variable result and return values to worksheet TEXTJOIN = Left(result, Len(result) - Len(delimiter)) End Function

**6.2 Where do I put this code?**

- Press Alt + F11 to open the Visual Basic Editor (VBE).
- Press with left mouse button on "Insert" on the menu.
- Press with left mouse button on "Module".
- Copy code above.
- Paste it to the code module.
- Exit VB Editor.

**6.3 How do I use it?**

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

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

### 6.5 UDF Syntax

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

## 7. TEXTJOIN function - how to add row delimiting characters

The TEXTSPLIT function allows you to split a string using both column and row delimiting characters, however, the TEXTJOIN function lets you only use a column delimiting character.

There is a workaround to add row delimiting characters, add your row delimiting character after the last cell on each row, see the image above cell range E3:E5.

You can now use the TEXTSPLIT function to split the string to create a 2D array, 2D array is an array with both columns and rows.

### 'TEXTJOIN' Function examples

The following 28 articles have formulas containing the TEXTJOIN function.

Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]

This article demonstrates Excel formulas that extract unique distinct values from a cell range and concatenates the values using any […]

The formula in cell B10 extracts unique distinct values from cell range B2:d4 that contains the string specified in cell […]

The array formula in cell B10 extracts unique distinct values from cell range B2:D4 that begins with a given condition […]

The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]

The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]

The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]

EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]

Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]

The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]

The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]

Question: How to extract email addresses from this sheet? (See pic below) Answer: It depends on how the emails are […]

The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]

This article demonstrates an array formula that extracts all characters except numbers from a cell, cell C3 contains the formula […]

Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]

I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]

Table of Contents Identify all characters in a cell value Identify all characters in a cell value - Excel 365 […]

This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

This article demonstrates several ways to check if a cell contains any value based on a list. The first example […]

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

In this post I am going to add one more function to the weekly schedule I built in a previous […]

This article demonstrates formulas that rearrange values in a cell range to a single column. Table of Contents Rearrange cells […]

This article demonstrates a few different formulas that extract values from the table column header names and the right-most column. […]

The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]

This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]

This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]

This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]

What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]

### Functions in 'Text' category

The TEXTJOIN function function is one of many functions in the 'Text' category.

### 17 Responses to “How to use the TEXTJOIN function”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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

I followed the instructions but when I type TEXTJOIN( in a cell I get an error "Ambiguous name detected: TEXTJOIN".

I am new to using Visual Basic so am probably doing something silly but it doesn't seem to recognize the TEXTJOIN function I've created

Any ideas?

josh,

This error occurs when there are multiple UDFs with the same name in a module.

Not working for me. Get VALUE! error

lesli

Can you post your formula here?

Also after finding the function, it commits to the SS all lowercase not upper. That is an alert to me as my other functions keep the exact name case format.

I followed the instructions for TEXTJOIN unique values and got the curly brackets, but cell was empty.

It may have something to do with the fact that my array is a list of horizontal cells rather than vertical ?

I tried "column" instead of "row" in the the function but got "#value!"

Please help.

Can you post the formula?

Hi - Thanks for posting this - as I've had to make my spreadsheet backwards compatible with older versions of Excel.

I've been using this UDF for some time but have found that myself and others receive an occasional error where the first line of code (Function TEXTJOIN1(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)) is highlighted in the VB editor with the following error being thrown on Excel opening the file: "Automation error Catastrophic failure". I've done some research and have yet to figure out why this may be throwing an error on some machines intermittently and not others. Some posts point to using 'Range' instead of 'String' ... which I've tried with no luck.

Wasn't sure if anyone else had encountered this?

Thanks!

ASmyth

"Automation error Catastrophic failure"That is an error I never have encountered or heard of before.

This StackOverflow page suggests a few solutions:

https://stackoverflow.com/questions/31888880/automation-error-catastrophic-failure-excel-vba

Hi, I have used this formula {=TEXTJOIN(",",TRUE,IF(I2=$A$2:$A$4785,$B$2:$B$4785,""))} to link text from different cells which has worked for the most of my data, but for some I get the #Value error appear. I cannot see why I am getting this as the cells are formatted the same and there is nothing seemingly different. Any ideas?

Thanks and thanks for creating the above, its really useful

Is there a way to use the textjoin function with more than 3000 records?

Mike,

The TEXTJOIN function returns a #VALUE! error if the returned value has more than 32767 characters.

https://support.office.com/en-us/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c

Hello,

I have used the VBA to add this formula to an older Excel. I have one issue with it though, blank columns return a #VALUE! error.

I use the formula to add all the items in rows in a range

=TEXTJOIN(" ",TRUEX5:X48)

Each column from X to BC has this formula in it. Not every column is used every day, but the product changes day by day. Is there a way to avoid this error if the column is blank?

I have found that if I make the delimiter "" then the error does not appear, but then I have spacing issues left behind.

Asslam-O-Alikum!

In Excel 2016 I used the above given VB code for txetjoin function(TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...), but for ignore_empty either True or False, it is not ignoring the empty cell the example is given below:

the formula used is

=textjoin(", ",TRUE,IF(I11:I15="✓",B11:B15," "))

& the result is

, , , M/S Sohail Engineering Corporation, Badami Bagh, Lahore.,

kindly guide me.

This is one of the best page I have seen with excellent step by step instructions in Layman language. Thank you so much.