Excel TEXTJOIN Function
TEXTJOIN Function
The TEXTJOIN function is a premade function in Excel, which joins the text from several cells into one cell, with a delimiter between each value.
A delimiter is the text that separates the values, like a comma and a space. TEXTJOIN can also skip empty cells.
It is typed =TEXTJOIN and has the following parts:
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Note: TEXTJOIN is available in Excel 2019 and later, Excel for Microsoft 365 and Excel for the web. It is not available in Excel 2016 or earlier versions.
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
Delimiter: The text to put between the values, inside double quotes. For example ", " for a comma and a space, or "/" for a slash.
Ignore_empty: TRUE skips empty cells. FALSE includes them, which can give two delimiters in a row.
Text1: The first text to join. This can be a cell, a range like A2:A11, or text inside double quotes.
Text2, ...: Optional. More text to join, separated by commas.
How to use the TEXTJOIN function:
- Select a cell (
F2) - Type
=TEXTJOIN - Double click the TEXTJOIN command
- Type the delimiter inside double quotes (
", ") - Type (
,) - Type
TRUEto skip empty cells - Type (
,) - Mark the range to join (
A5:A7) - Hit enter
Let's have a look at an example!
These are the first ten Pokemon from the table on the XLOOKUP page, with their types. Copy them and paste them into cell A1 to follow along:
Example data
A B C 1 Name Type 1 Type 2 2 Bulbasaur Grass Poison 3 Ivysaur Grass Poison 4 Venusaur Grass Poison 5 Charmander Fire 6 Charmeleon Fire 7 Charizard Fire Flying 8 Squirtle Water 9 Wartortle Water 10 Blastoise Water 11 Caterpie Bug
Type the label Fire Pokemon in E2.
Use the TEXTJOIN function to make a list of the Fire Pokemon in one cell. They are in A5:A7:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Type 1 | Type 2 | |||
| 2 | Bulbasaur | Grass | Poison | Fire Pokemon | Charmander, Charmeleon, Charizard | |
| 3 | Ivysaur | Grass | Poison | |||
| 4 | Venusaur | Grass | Poison | |||
| 5 | Charmander | Fire | ||||
| 6 | Charmeleon | Fire | ||||
| 7 | Charizard | Fire | Flying | |||
| 8 | Squirtle | Water | ||||
| 9 | Wartortle | Water | ||||
| 10 | Blastoise | Water | ||||
| 11 | Caterpie | Bug |
=TEXTJOIN(", ", TRUE, A5:A7)
TEXTJOIN joins the names in A5:A7, and puts a comma and a space between them. There is no delimiter after the last name.
Skip Empty Cells
Some Pokemon have two types, and some have only one. Join Type 1 and Type 2 into one cell, with a slash between them.
Type the label Types in D1, and the formula in D2. Fill it down to D11:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Type 1 | Type 2 | Types |
| 2 | Bulbasaur | Grass | Poison | Grass/Poison |
| 3 | Ivysaur | Grass | Poison | Grass/Poison |
| 4 | Venusaur | Grass | Poison | Grass/Poison |
| 5 | Charmander | Fire | Fire | |
| 6 | Charmeleon | Fire | Fire | |
| 7 | Charizard | Fire | Flying | Fire/Flying |
| 8 | Squirtle | Water | Water | |
| 9 | Wartortle | Water | Water | |
| 10 | Blastoise | Water | Water | |
| 11 | Caterpie | Bug | Bug |
=TEXTJOIN("/", TRUE, B2:C2)
Bulbasaur has two types, so the result is Grass/Poison.
Charmander has no Type 2. Because ignore_empty is TRUE, TEXTJOIN skips the empty cell C5, and the result is just Fire.
With FALSE, the empty cell is included, and the result ends with a slash:
| A | B | C | D | |
|---|---|---|---|---|
| 5 | Charmander | Fire | Fire/ |
=TEXTJOIN("/", FALSE, B5:C5)
In most cases, you want TRUE.
Join the Pokemon of One Type
In the first example, you marked the Fire Pokemon yourself. With IF inside TEXTJOIN, Excel finds the Pokemon of a type for you, wherever they are in the list.
Type the labels Search Type in E4 and Pokemon in E5. Then type Water in F4, and the formula in F5:
| E | F | |
|---|---|---|
| 4 | Search Type | Water |
| 5 | Pokemon | Squirtle, Wartortle, Blastoise |
=TEXTJOIN(", ", TRUE, IF(B2:B11=F4, A2:A11, ""))
IF checks every row in B2:B11. When the type is the same as in F4, IF returns the name. Otherwise it returns an empty text "".
Because ignore_empty is TRUE, TEXTJOIN skips the empty texts, and joins only the names.
Try another type in F4, like Grass. The result changes to Bulbasaur, Ivysaur, Venusaur.
Note: This formula works as shown in Excel for Microsoft 365 and Excel 2021 or later, where Excel checks the whole range at once. In Excel 2019, press Ctrl+Shift+Enter instead of Enter to confirm the formula.
In Excel for Microsoft 365 and Excel 2021 or later, you can also use the FILTER function to get the same list:
=TEXTJOIN(", ", TRUE, FILTER(A2:A11, B2:B11=F4))
TEXTJOIN vs CONCAT
The CONCAT function also joins text, but it does not add a delimiter. =CONCAT(A5:A7) returns CharmanderCharmeleonCharizard.
| CONCAT | TEXTJOIN | |
|---|---|---|
| Delimiter | No, you type it between the values yourself | Yes, typed once for all values |
| Leave out the delimiter for empty cells | No, =CONCAT(B5, "/", C5) returns Fire/ | Yes, when ignore_empty is TRUE |
| Join a range, like A5:A7 | Yes | Yes |
If you need a delimiter between many values, TEXTJOIN is usually the easier choice.
