Excel TEXTJOIN Function Examples – Excel & Google Sheets

This tutorial demonstrates how to use the Excel TEXTJOIN Function in Excel to combines text from multiple strings.

Textjoin Main Function

TEXTJOIN Function Overview

The TEXTJOIN function combines text from multiple strings you specify the choice of delimiter and whether to ignore blanks.

To use the TEXTJOIN Excel Worksheet Function, select a cell and type:

Textjoin-Function

(Notice how the formula inputs appear)

TEXTJOIN function Syntax and inputs:

delimiter – Separator between each text.

ignore_empty – Whether to ignore empty cells or not.

text1 – First text value or range.

text2 – [optional] Second text value or range.

How to use the TEXTJOIN Function in Excel:

The TEXTJOIN function combines text from multiple strings you specify the choice of delimiter and whether to ignore blanks. It is available in Excel 2016 and above.

=TEXTJOIN(" ",false,B3:C3)

Textjoin-01

The above combines First Name and Last Name (column B and C) with a space as the delimiter (the character that splits the word) and a choice to include blanks (next example will be more obvious).

TEXTJOIN to Include Blanks

Although some cells may be blanks, it may be important to know they are blanks after combining them.

=TEXTJOIN(";",FALSE,B3:E3)

Textjoin-02

Using FALSE as the second argument, it combines them with a semi-colon (chosen as the delimiter) despite some of them being blanks. When you need to split them up again, this proves useful with Text to Columns.

TEXTJOIN to Ignore Blanks

It would not make sense to include blanks if the scenario is to combine the salutation with the names like this below:

=TEXTJOIN(" ",FALSE,B3:E3)

Textjoin 02.2

You can see the obvious ones in F5 and F6 where there is an extra space between the first and last names.

In this scenario, it would be better to ignore blank cells if they are not filled with a TRUE as the second argument.

=TEXTJOIN(" ",TRUE,B3:E3)

Textjoin 03

 

TEXTJOIN with 1 and 0

If you’ve used VLOOKUP, you might know Excel recognizes TRUE as 1 and FALSE as 0. They can be used interchangeably in most cases. And in here as well. There is no impact on choosing the text or number. Just preference (and typing speed).

Textjoin-04

TEXTJOIN with Condition

You might want to combine the text, only if they meet a condition. In this scenario, you might want to join the Full Names with commas as the delimiter, only if it’s “Yes” in Worked Overtime (column C).

{=TEXTJOIN(", ",TRUE,IF(C3:C7="Yes",B3:B7,""))}

Textjoin-05

This is an array formula.

If you’re using a version of Excel prior to 2019, you must press CTRL + SHIFT + ENTER instead of ENTER after typing the formula. You should see curly brackets surrounding it when done right (DO NOT manually type the curly brackets).

If you’re using Excel 2019 or Office 365, you can enter the formula normally.

TEXTJOIN with Different Delimiters

You can use array constants (the curly brackets) to input multiple delimiters like this:

=TEXTJOIN({"-","."},1,B3:D3)

Textjoin 06

It will know to use a dash (-) as the first delimiter and period (.) as the second one.

TEXTJOIN with Number/Date

Do note that TEXTJOIN is a text function. Upon using TEXTJOIN, the result is a text. For instance, you won’t be able to sum up these numbers in cell F3 after using TEXTJOIN.

Textjoin 08

The same thing happens for dates as they are recognized as serial numbers and not text. You may not need to sum dates, but it doesn’t work well in filters and PivotTables.

Textjoin 07

To overcome the issues above, you can use VALUE to convert from text to values.

Textjoin-09

Textjoin 10

Other Example of TEXTJOIN

Reverse a text string

You can reverse a text string by combining MID function with TEXTJOINT as follow.

=TEXTJOIN("",1,MID(A2,{10,9,8,7,6,5,4,3,2,1},1))

Which would give us the following results:

reverse string

TEXTJOIN Function in Google Sheets

The TEXTJOIN function works almost the same in Google Sheets.

Textjoin G Function

 

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!