What it does
The first argument of TEXTJOIN is the delimiter: ", ", " - ", ";" or CHAR(10) for a line break. The second decides what happens to empty cells: TRUE skips them, FALSE adds a delimiter for them too, so you see two delimiters side by side. Then come the cells or ranges to join.
Its real strength shows when it is combined with a condition. In Microsoft 365, =TEXTJOIN(", ",TRUE,FILTER(A2:A9,B2:B9="West")) joins only the names in the West region. Without FILTER, IF(B2:B9="West",A2:A9,"") does the same, because rows that fail the test become empty text and are skipped; in Excel 2019 that formula must be entered as an array formula with Ctrl+Shift+Enter.
TEXTJOIN is available from Excel 2019. To split the joined text into parts again, use TEXTSPLIT; to join without a delimiter, use CONCAT.
Syntax
=TEXTJOIN(delimiter, ignore_empty, text1, ...)=METİNBİRLEŞTİR(sınırlayıcı; boşları_yoksay; metin1; ...)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
delimiter | Required | The text placed between the values, e.g. ", ". With empty text "" the values are written next to each other. |
ignore_empty | Required | TRUE: empty cells and empty text are skipped. FALSE: a delimiter is added for them too. |
text1 | Required | The cells, ranges or texts to join. Several ranges can be given; they are joined in order. |
Examples
1Join a list and skip the blanks
One row of the attendee list is empty. The formula in C2 skips it; the one in D2 has FALSE as its second argument, so it adds a delimiter for the empty cell as well.
=TEXTJOIN(", ",TRUE,A2:A6)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Attendee | Blanks skipped | Blanks included | |
| 2 | Emma | Emma, Liam, Olivia, Noah | Emma, Liam, , Olivia, Noah | |
| 3 | Liam | |||
| 4 | ||||
| 5 | Olivia | |||
| 6 | Noah |
- C2
=TEXTJOIN(", ",TRUE,A2:A6) - D2
=TEXTJOIN(", ",FALSE,A2:A6)
C2 shows Emma, Liam, Olivia, Noah. In D2, two commas appear side by side where the empty row was: Emma, Liam, , Olivia, Noah.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2List the reps of each region in one cell
The formula in E2 is filled down. IF returns the name where the region matches the one in column D and empty text elsewhere; TEXTJOIN skips the empty texts.
=TEXTJOIN(", ",TRUE,IF($B$2:$B$7=D2,$A$2:$A$7,""))| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Region | Reps | |
| 2 | Emma | West | West | Emma, Olivia, Mia | |
| 3 | Liam | North | North | Liam, Ava | |
| 4 | Olivia | West | South | Noah | |
| 5 | Noah | South | |||
| 6 | Ava | North | |||
| 7 | Mia | West |
- E2 (filled down to E4)
=TEXTJOIN(", ",TRUE,IF($B$2:$B$7=D2,$A$2:$A$7,""))
The West row shows Emma, Olivia, Mia, the North row Liam, Ava and the South row Noah. In Microsoft 365 you can get the same result with FILTER.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Build an address line and skip empty parts
The address parts are in separate columns, and some companies have no second address line. Because blanks are skipped, no extra commas appear.
=TEXTJOIN(", ",TRUE,B2:E2)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Company | Address 1 | Address 2 | Town | Postcode | Address |
| 2 | Brown & Sons | 12 Station Road | Unit 3 | Leeds | LS1 4AB | 12 Station Road, Unit 3, Leeds, LS1 4AB |
| 3 | Northgate Ltd | 45 High Street | York | YO1 7HU | 45 High Street, York, YO1 7HU | |
| 4 | Harbour Trade | 8 Quay Side | Block B | Bristol | BS1 5TR | 8 Quay Side, Block B, Bristol, BS1 5TR |
| 5 | Star Market | 3 Market Lane | Hull | HU1 2AB | 3 Market Lane, Hull, HU1 2AB |
- F2 (filled down to F5)
=TEXTJOIN(", ",TRUE,B2:E2)
F2 shows 12 Station Road, Unit 3, Leeds, LS1 4AB; F3, which has no second address line, becomes 45 High Street, York, YO1 7HU.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #VALUE!
Cause: The joined text exceeds the 32,767-character limit.
Fix: Join a smaller range or split the result.
- #CALC!
Cause: The FILTER inside found no matching rows and returned an empty-array error.
Fix: Give FILTER empty text as its third argument:
FILTER(A2:A9,B2:B9="West","").- Delimiters side by side
Cause: ignore_empty was
FALSE, so a delimiter was added for each empty cell: "Emma, , Liam".Fix: Set the second argument to
TRUE.
Good to know
- To show the list one item per line, use
CHAR(10)as the delimiter and turn on Wrap Text. - To join each value only once, combine it with UNIQUE in Microsoft 365:
=TEXTJOIN(", ",TRUE,UNIQUE(B2:B20)).
In Nexell
Get Nexell to do it from a sentence
Make a summary that lists the reps of each region in one cell, separated by commas
Convert to tableBring your rep list into the tool and type this. Nexell builds a summary that gathers each region's reps into one cell and shows it in a preview first; once you approve, you download it as .xlsx.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I combine a column of values with commas in Excel?
Use =TEXTJOIN(", ",TRUE,A2:A20). Because the second argument is TRUE, empty cells in between are skipped.
How do I join only the values that meet a condition?
In Microsoft 365: =TEXTJOIN(", ",TRUE,FILTER(A2:A20,B2:B20="West")). Without FILTER, =TEXTJOIN(", ",TRUE,IF(B2:B20="West",A2:A20,"")) gives the same result.
Related functions
- CONCATARALIKBİRLEŞTİR · Joins texts and ranges.
- CONCATENATEBİRLEŞTİR · Joins texts.
- TEXTSPLITMETİNBÖL · Splits text by delimiters (array).
- FILTERFİLTRE · Filters rows that meet a condition. Returns the third argument or #CALC! if nothing matches.
- IFEĞER · Returns one value if a condition is true and another if false.