How to Use TEXTJOIN Function in Excel (3 Examples)

Excel TEXTJOIN Function Quick View

TEXTJOIN is one of the most important and widely used functions in Excel that has been available from Excel 2019.

Today I will be showing how you can use this TEXTJOIN function in Excel.

TEXTJOIN Function in Excel (Quick View)

Excel TEXTJOIN Function Quick View

Download Practice Workbook

Excel TEXTJOIN Function (Syntax and Argument)

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 

Syntax of the TEXTJOIN Function

The Syntax of the TEXTJOIN functions is:

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

Argument

Argument Required or Optional Value
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 to be joined.
  • 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.

Excel TEXTJOIN Function: 3 Examples

1. TEXTJOIN Function with Single Cells

Here we have a data set with the IDs, Names, and Email IDs of some employees of a company named Marco Group.

Data Set for Excel TEXTJOIN Function

We can use the TEXTJOIN function to merge all information of each employee into a single text value separated by commas(,).

The formula for the first employee will be:

=TEXTJOIN(", ",TRUE,B4,C4,D4)

TEXTJOIN Function with Single Values

As you can see, we have merged all information of each into single cells using the TEXTJOIN function.

Note: Here 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.

2. TEXTJOIN Function with a Range of Values

You can use the TEXTJOIN Function of 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:

=TEXTJOIN(", ",TRUE,C4:C8)

TEXTJOIN Function with a Range of Values

3. TEXTJOIN Function with Another Function

We can use the TEXTJOIN function with another function of Excel 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 of 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.

Data Set for TEXTJOIN with FILTER Function of Excel

Our Objective is to use the TEXTJOIN function along with the FILTER function to return the years in which Brazil became champion, in a single cell.

First, we will use the FILTER formula:

=FILTER(B4:B24,D4:D24="Brazil")

This will return an array consisting of the years in which Brazil became champion (See the FILTER function for details).

Next, we will use the TEXTJOIN function to merge the years in a single cell, separated by commas(,).

So the complete formula will be:

=TEXTJOIN(", ",TRUE,FILTER(B4:B24,D4:D24="Brazil"))

TEXTJOIN with FILTER Function in Excel

Similarly, you can use the TEXTJOIN function with any Array Formula to merge the result into a single cell.

Common Errors with Excel TEXTJOIN Function

Error When They Show
#VALUE! Shows when any argument in the function is missing, or any argument is of the wrong data type.

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.

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