How to Use TEXTJOIN Function in Excel (3 Suitable Examples)

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. Today, I will show you how you can use this TEXTJOIN function in Excel effectively with appropriate illustrations.


Download Practice Workbook

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


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.


3 Suitable Examples to Use TEXTJOIN Function in Excel

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.

excel textjoin function


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!

Steps:

  • 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]

Concatenate Specific Cells Using TEXTJOIN Function in Excel

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

Read more: How to Concatenate Multiple Cells in Excel


Example 2: 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!

Steps:

  • 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, Alfred Moyes.

Merge a Range of Values by Applying TEXTJOIN Function in Excel

Read more: Combine Multiple Columns into One Column in Excel


Example 3: 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!

Steps:

  • First, write down 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.

Combine Texts with Multiple Criteria by Nesting TEXTJOIN and FILTER Functions

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.

Conclusion

Therefore, you can use the TEXTJOIN function of Excel to merge an array or range of values into a single cell. Do you have any questions? Feel free to ask us.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo