In this article, we will learn to use the **TEXTJOIN** function of Excel. We will also explore various examples of using the TEXTJOIN function in different scenarios. TEXTJOIN is one of the most important and widely used functions in Excel that has been available since Excel 2019. Using this function, you can easily concatenate specific cells. Here, we will show you how you can use this **TEXTJOIN** function in Excel effectively with appropriate illustrations.

**Table of Contents**hide

## Introduction to TEXTJOIN Function in Excel

**Summary**

- Concatenates a list or range of text strings into a single string using a delimiter.
- Can include both empty cells and non-empty cells.
- Available from
**Excel 2019**.

**Syntax**

The **syntax** of the **TEXTJOIN** functions is:

`=TEXTJOIN(delimiter,ignore_empty,text1,...)`

**Arguments Explanation**

Arguments | Required/Optional | Explanation |
---|---|---|

delimiter |
Required | The delimiter by which the concatenated texts will be separated. |

ignore_empty |
Required | Tells whether to ignore the empty cells in the range or not. |

text1 |
Required | The first text string to be joined. |

[text2] |
Optional | The second text string is to be joined. |

… | … | … |

… | … | … |

**Notes**- You can use a maximum number of 252 texts to join, like
**text1, text2**, …, etc. up to**text252**. - The
**text1, text2,**…, etc arguments can be numbers also. Not necessary that they must be strings. The**TEXTJOIN**function can join numbers too.

**Return Value**

Returns a text string by joining all the given texts separated by the delimiter.

## How to Use TEXTJOIN Function in Excel: 7 Suitable Examples

Consider the following dataset. Let’s use this dataset to demonstrate what actions to take while using the **TEXTJOIN **function. We will concatenate specific cells, merge a range of cells using the **TEXTJOIN **function, and nest the **TEXTJOIN **and **FILTER **functions as well in Excel. Here’s an overview of the dataset for today’s task.

### Example 1: Concatenate Specific Cells Using TEXTJOIN Function in Excel

Here we have a data set with the **IDs, Names,** and **Email IDs** of some employees of a company named **Marco Group**. We can use the **TEXTJOIN** function to merge all information about each employee into a single text value separated by **commas(,)**. Let’s follow the instructions below to learn!

- First of all, type the following formula in cell
**E5**for the first employee.

`=TEXTJOIN(", ",TRUE,B5,C5,D5)`

- Where,
**“, “**is the**delimiter**,**TRUE**is the**ignore_empty, B5, C5,**and**D5**is the**text 1, text2,**and**text 3**respectively of the**TEXTJOIN**function. - Hence, simply press
**Enter**on your keyboard. As a result, you will be able to concatenate specific cells which is the return of**the TEXTJOIN function**. The return is**101, Frank Orwell, [email protected].**

- Further,
**AutoFill**the**TEXTJOIN**function to the rest of the cells in the column. - As you can see, we have merged all information of each into single cells using the
**TEXTJOIN**function.

**Notes**- We have used
**numbers**(**Employee ID**) as well as**strings**(**Name**and**Email ID**) inside the**TEXTJOIN**function. - The
**TEXTJOIN**function can join both**numbers**and**strings**.

### Example 2: Joining Cells With Different Delimiters

- Apply the following formula in cell
**E5**to join cells with different delimiters >> press**ENTER**>> drag the**Fill Handle**up to cell**E9**.

`=TEXTJOIN({", "," : "},TRUE,B5:D5)`

Here,** {“, “,” : “}** are the set of delimiters we will use, and **B5:D5** is the range of cells to join.

As a result, you will have the selected cells joined by two delimiters as shown in the image below.

### Example 3: Merge a Range of Values by Applying TEXTJOIN Function in Excel

You can use the **TEXTJOIN** function in Excel to merge a range of values into a single cell. In the above data set, you can use the **TEXTJOIN** function to merge the names of the **first five employees** using this formula. Let’s follow the instructions below to learn!

- Insert the below formula in cell
**E5.**

`=TEXTJOIN(", ",TRUE,C5:C9)`

- After that, press
**Enter**on your keyboard to get the return of**the TEXTJOIN function**. The return is**Frank Orwell, Natalia Austin, Jennifer Marlo, Richard King,**and**Alfred Moyes.**

### Example 4: Joining Texts and Dates Using TEXTJOIN Function

- Use the formula given below in cell
**E5**to join the texts and dates together >> hit**ENTER**>> drag the**Fill Handle**up to cell**E9**.

`=TEXTJOIN(", ",TRUE,C5,TEXT(D5, "m/d/yyyy"))`

- Consequently, the texts and dates will be concatenated as demonstrated in the following image.

**Formula Breakdown**

**TEXT(D5, “m/d/yyyy”)**→ Here,**the TEXT function**is used to convert the numerical date value to date format.**D5**→ It is theargument.*value***“m/d/yyyy”**→ This refers to theargument.*format_text***Output**→**“1/1/2023”**.

**TEXTJOIN(“, “,TRUE,C5,TEXT(D5, “m/d/yyyy”))**→**TEXTJOIN(“, “,TRUE,C5,”1/1/2023”)****“, “**→ It is theargument.*delimiter***TRUE**→ This represents theargument.*ignore_empty***C5**→ It refers to theargument.*text1***“1/1/2023”**→ This indicates theargument.*[text2]***Output**→**Frank Orwell, 1/1/2023**.

**Notes****TEXT**function for matching the source formatting, dates will be added as numbers beside the text like this “

**Frank Orwell, 44927**”.

### Example 5: Concatenating Texts with Line Breaks

- Use the following formula in cell
**E5**>> press**ENTER**>> drag the**Fill Handle**to copy the formula for the remaining cells.

`=TEXTJOIN(CHAR(10), TRUE, C5:D5)`

Here, **the CHAR function** is used to give a line break between two texts, and **C5:D5** denotes the range of cells to concatenate.

**Notes****Wrap Text**option. Otherwise, the output will be shown in a single line.

### Example 6: Using TEXTJOIN Function with Criteria

- Apply the formula given below in cell
**E5**>> hit**ENTER**>> use the**Fill Handle**to copy the formula for the remaining cells.

`=TEXTJOIN(", ",TRUE,IF(B5<130,B5,""),IF(B5<130,C5,""))`

- As a result, you have the concatenated
**Employee Name**, and**Employee ID**where the**Employee ID**is less than**130**.

**Formula Breakdown**

**IF(B5<130,B5,””)**→**The IF function**checks whether the**Employee ID**is less than**130**or not.**B5<130**→ This is theargument.*logical_test***B5**→ It refers to theargument.*[value_if_true]***“”**→ This indicates theargument.*[value_if_false]***Output**→**101**.

**IF(B5<130,C5,””)**→ This**IF**function also checks for the same condition as the previous one.**Output**→**“Frank Orwell”**.

**TEXTJOIN(“, “,TRUE,IF(B5<130,B5,””),IF(B5<130**,C5,””)) →**TEXTJOIN(“, “,TRUE,101,”Frank Orwell”)****Output**→**101, Frank Orwell**.

### Example 7: Combine Texts with Multiple Criteria by Nesting TEXTJOIN and FILTER Functions

We can use the **TEXTJOIN** function with another Excel function to merge the result returned by that function into a single cell. This is mostly used with the **FILTER** function of Excel, as **FILTER** is a widely used function in Excel that returns an array.

Here we have a new data set with the **Years, Host Countries, Champions,** and **Runners-ups **of the **FIFA World Cup** from **1930 **to **2018.**

Our objective is to use the **TEXTJOIN** function and **the FILTER function** to return the years in which **Brazil** became the champion, in a single cell. Let’s follow the instructions below to learn!

- First, write the following formula in cell
**G5**to merge the years in a single cell, separated by commas**(,).**

`=TEXTJOIN(", ",TRUE,FILTER(B5:B25,D5:D25="Brazil"))`

- As a result, you can be able to use the
**TEXTJOIN**function with any**array formula**by hitting**Enter**to merge the result into a single cell.

**Formula Breakdown**

**FILTER(B5:B25,D5:D25=”Brazil”)**will return an array of the years in which**Brazil**became the champion.- After that,
**TEXTJOIN(“, “,TRUE,FILTER(B5:B25,D5:D25=”Brazil”))**will concatenate the years in which**Brazil**became the champion in one cell.

## Reasons Behind TEXTJOIN Function Not Working in Excel

Errors | When They Show |
---|---|

#VALUE! |
Shows when any argument in the function is missing, or any argument is of the wrong data type. |

#NAME! |
While using the older version (before Excel 2019) which is not capable of the TEXTJOIN function. |

#NULL! |
This happens when we fail to separate the strings that we want to join with a comma. |

## Things to Remember

- Using the
**TEXTJOIN**function, you can join up to**252**text arguments. **TEXTJOIN**function is available in Excel from**Excel 2019**. So, if you are using an older version of Excel then you need to use alternatives to the**TEXTJOIN**function like**the CONCATENATE function**.

## Frequently Asked Questions

**1. What is the difference between the CONACTENATE function and the TEXTJOIN function?**

**Ans:** The **TEXJOIN **function allows you to define any delimiter you want which can’t be done by the **CONCATENATE **function. Additionally, it is possible to ignore any empty cell from the resulting string while using the **TEXTJOIN **function. But the **CONCATENATE **function can’t do that.

**2. What are some alternatives to the TEXTJOIN function?**

**Ans:** As an alternative, you can concatenate text using the **CONCATENATE **function. You can also use the **&** operator to join texts. But these alternatives are not as efficient as the **TEXTJOIN **function.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## Conclusion

We have come to the end of the article. In this article, we have tried to demonstrate the working mechanism and various use cases of the **TEXTJOIN** function. We hope that you will find this article helpful. If you have any questions, feel free to ask us in the comments section.

## Further Readings

**How to Use FIND Function in Excel****How to Use LEFT Function in Excel****How to Use LEN Function in Excel****How to Use Excel LOWER Function****How to Use MID Function in Excel****How to Use REPLACE Function in Excel****How to Use REPT Function in Excel****How to Use SEARCH Function in Excel****FIND and SEARCH Function in Excel****How to Use VALUE Function in Excel****How to Use TRIM Function in Excel****How to Use UPPER Function in Excel****How to Use DOLLAR Function in Excel****How to Use Excel TEXT Formula****How to Use Excel EXACT Function****How to Use CODE Function in Excel****Use FIXED Function in Excel****How to Use CLEAN Function in Excel****Use SUBSTITUTE function in Excel****How to Use RIGHT Function in Excel****How to use PROPER function in Excel**