In this Article
- TEXTJOIN Function Overview
- How to use the TEXTJOIN Function in Excel:
- TEXTJOIN to Include Blanks
- TEXTJOIN to Ignore Blanks
- TEXTJOIN with 1 and 0
- TEXTJOIN with Condition
- TEXTJOIN with Different Delimiters
- TEXTJOIN with Number/Date
- Other Example of TEXTJOIN
- Reverse a text string
- TEXTJOIN Function in Google Sheets
This tutorial demonstrates how to use the Excel TEXTJOIN Function in Excel to combines text from multiple strings.
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:
(Notice how the formula inputs appear)
TEXTJOIN function Syntax and inputs:
=TEXTJOIN (delimiter, ignore_empty, text1, [text2], ...)
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.
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.
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:
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 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 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).
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:
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.
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.
To overcome the issues above, you can use VALUE to convert from text to values.
Other Example of TEXTJOIN
Reverse a text string
You can reverse a text string by combining MID function with TEXTJOINT as follow.
Which would give us the following results:
TEXTJOIN Function in Google Sheets
The TEXTJOIN function works almost the same in Google Sheets.