Excel 2019 or laterExamples need Excel 2024 or laterText

TEXTJOIN

TEKST.COMBINEREN in Dutch Excel

Joins texts and whole ranges into one cell with a separator of your choice between them, optionally skipping empty cells.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

ABC
1Debtor, no replyEmail line
2Dekker Techniek BVDekker Techniek BV, Smit Groen BV, Van Dam Transport=TEXTJOIN(", ", TRUE, A2:A4)=TEXTJOIN(", "; TRUE; A2:A4)
3Smit Groen BV
4Van Dam Transport

Result: Dekker Techniek BV, Smit Groen BV, Van Dam Transport

=TEXTJOIN(", ", TRUE, A2:A4)
=TEXTJOIN(", "; TRUE; A2:A4)

What TEXTJOIN does and when to use it

What it does: it joins a list of texts into one cell with a separator between them, for example every debtor that has not answered, separated by commas.

When to use it: for summary cells that list names or codes, for labels built from several columns, and for conditional lists with FILTER or IF inside. It replaces joining with & and CONCAT, which have no separator, and it can skip blank cells.

Three main gains:

  • Separator built in: you give it once instead of typing it between every item.
  • Skips blank cells: ignore_empty set to TRUE avoids doubled separators.
  • Whole ranges: you pass A2:A20 as one argument instead of linking every cell with &.

How Excel evaluates it

Excel works through the arguments in this order:

  • it reads delimiter and ignore_empty first
  • it walks through every text argument in order, and through every range cell by cell, row by row
  • it converts each value to text; with ignore_empty TRUE, empty cells and empty texts are skipped
  • it inserts the delimiter between every two remaining items, and the result is one text in one cell

If the joined text would be longer than 32,767 characters, the cell limit, the result is #VALUE!.

Watch out: A joined text is for reading, not for calculating: when the list has to be sorted, counted or used in further formulas, keep it as a spilled column instead.

Mental model

Beads on a string with a knot between every two beads: empty spaces in the box are either skipped or get a knot of their own.

Terms used on this page: argument, range, cell

Syntax

Official TEXTJOIN documentation
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
TEXTJOIN(delimiter; ignore_empty; text1; [text2]; ...)
ArgumentTypeMeaning
delimiterRequiredThe text placed between the items, in quotes, such as ", ".
ignore_emptyRequiredTRUE skips empty cells; FALSE keeps them and doubles the separator.
text1RequiredThe first text, cell, range or array to join.
text2OptionalMore texts, cells or ranges; 252 text arguments in total, counting text1.

Return value:One text: every item joined, with delimiter between them

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

One line with every debtor that has not answered

You sent confirmation requests for the receivables balance at 31 December 2025. Three debtors have not answered yet, and you need them as one comma-separated line for the reminder email to the client. Typing the commas by hand is slow and goes wrong when a debtor answers or a new one is added.

ABCD
1Debtor, no replyEmail line
2Dekker Techniek BVDekker Techniek BV, Smit Groen BV, Van Dam Transport=TEXTJOIN(", ", TRUE, A2:A4)=TEXTJOIN(", "; TRUE; A2:A4)
3Smit Groen BV
4Van Dam Transport
5
Formula cell (C2)Input

Formula in C2

=TEXTJOIN(", ", TRUE, A2:A4)
=TEXTJOIN(", "; TRUE; A2:A4)

Result: Dekker Techniek BV, Smit Groen BV, Van Dam Transport

Step by step
  1. Excel reads the delimiter: a comma followed by a space. It is text, so it sits between quotes in the formula.
  2. It reads ignore_empty: TRUE. None of A2:A4 is empty, so here it changes nothing.
  3. It walks through A2:A4 from top to bottom and collects three names: Dekker Techniek BV, Smit Groen BV, Van Dam Transport.
  4. It puts the delimiter between every two names, 2 times for 3 names, and nothing after the last one: Dekker Techniek BV, Smit Groen BV, Van Dam Transport.
  5. Clear A3 because Smit Groen BV has answered: with ignore_empty TRUE the line becomes Dekker Techniek BV, Van Dam Transport, without a gap.

Watch out: Pasting the cell into an email client sometimes brings the formula's quotes along. Paste as values first, then copy from the formula bar.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

TEXTJOIN accepts a whole range as one argument and handles the separators itself, so the number of commas always matches the number of debtors. Copy C2 and paste it as values when you want plain text for the email.

More to watch out for

  • A name that itself contains a comma, such as 'Smit, Groen BV', makes the list ambiguous. Use a semicolon as the delimiter in that case.

Variations

=TEXTJOIN("; ", TRUE, A2:A4)
=TEXTJOIN("; "; TRUE; A2:A4)

Semicolons instead of commas, which is what most email programs expect between addresses, and safe when a name contains a comma.

Intermediate

Build a journal line key from parts, skipping blanks

In the XAF export you want one readable key per journal line, joined with slashes, to match against the client's specification. The key is built from four parts:

  • journal
  • entry no.
  • relation
  • invoice no.

Not every line has every part: bank lines often have no relation, and manual MEM entries have neither relation nor invoice no. The formula shown is the one for the BNK line in row 3; the other rows get their own key in the same way.

ABCDEF
1JournalEntry no.RelationInvoice no.Key
2VRK25-0388D10012025-1041
3BNK25-05772025-1088BNK / 25-0577 / 2025-1088=TEXTJOIN(" / ", TRUE, A3:D3)=TEXTJOIN(" / "; TRUE; A3:D3)
4MEM25-0601
5INK25-0630C20012025-3302
6
Formula cell (E3)Input

Formula in E3

=TEXTJOIN(" / ", TRUE, A3:D3)
=TEXTJOIN(" / "; TRUE; A3:D3)

Result: BNK / 25-0577 / 2025-1088

Step by step
  1. The formula sits in E3, for the BNK line. The reference A3:D3 is relative, so the same formula one row up would read A2:D2.
  2. Excel reads the four cells left to right: BNK, 25-0577, an empty C3, 2025-1088.
  3. ignore_empty is TRUE, so the empty C3 is skipped. Three items remain.
  4. The delimiter ' / ' goes between them: BNK / 25-0577 / 2025-1088.

  5. With FALSE as the second argument, the empty C3 would count as an item and the key would read BNK / 25-0577 / / 2025-1088, with two slashes and a double space.

    For the manual MEM entry in row 4, which has two empty cells at the end, FALSE would leave dangling slashes: MEM / 25-0601 / / , where TRUE gives MEM / 25-0601.

  6. Type D1004 in C3 and the key becomes BNK / 25-0577 / D1004 / 2025-1088.

Watch out: Exported text often holds a single space in a field that looks empty. That cell is not empty, so it is joined and you get 'BNK / 25-0577 / / 2025-1088'. Clean the source with TRIM, or check with LEN.

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

ignore_empty is what keeps the separators honest: an item that is missing does not exist for TEXTJOIN, so it gets no separator either. One formula therefore works for every journal line, whichever parts a line happens to have.

More to watch out for

  • Entry numbers stored as numbers lose their leading zeros, so 0412 joins as 412. Keep them as text, as the XAF export delivers them.

Variations

=TEXTJOIN(" / ", TRUE, A3, B3, D3)
=TEXTJOIN(" / "; TRUE; A3; B3; D3)

Pick separate cells instead of a range when the key should leave out a column, here the relation.

AdvancedExcel 2021 or later

List the booked invoices of each supplier in one cell

You reconcile the creditors ledger to the supplier statements. For each supplier you want one cell with every purchase invoice booked in the XAF export, comma-separated, to tick against the statement. The export grows with every extraction. The formula sits in E2 and is copied down to E5, so the ranges of the export must not move.

ABCDEF
1Invoice no.RelationRelationInvoices
2INK-1001C2001C2001INK-1001, INK-1003, INK-1005, INK-1008=TEXTJOIN(", ", TRUE, FILTER($A$2:$A$9, $B$2:$B$9=D2, "(none)"))=TEXTJOIN(", "; TRUE; FILTER($A$2:$A$9; $B$2:$B$9=D2; "(none)"))
3INK-1002C2002C2002
4INK-1003C2001C2003
5INK-1004C2003C2004
6INK-1005C2001
7INK-1006C2002
8INK-1007C2003
9INK-1008C2001
10
Formula cell (E2)Input

Formula in E2

=TEXTJOIN(", ", TRUE, FILTER($A$2:$A$9, $B$2:$B$9=D2, "(none)"))
=TEXTJOIN(", "; TRUE; FILTER($A$2:$A$9; $B$2:$B$9=D2; "(none)"))

Result: INK-1001, INK-1003, INK-1005, INK-1008

Step by step
  1. The inner function runs first. FILTER compares $B$2:$B$9 with D2, C2001: TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE, TRUE.
  2. It returns the matching invoice numbers from $A$2:$A$9 as a 4-item array: INK-1001, INK-1003, INK-1005, INK-1008. Inside TEXTJOIN this array does not spill; it is handed over as one argument.
  3. TEXTJOIN puts ', ' between the four invoice numbers: INK-1001, INK-1003, INK-1005, INK-1008, in one cell, E2.
  4. Copied to E3, the $ signs keep the export ranges fixed while D2 becomes D3, C2002: INK-1002, INK-1006. In E4, C2003: INK-1004, INK-1007.
  5. In E5 the relation is C2004, a supplier with no booked invoice: FILTER finds no row and returns its if_empty text, so E5 shows (none) instead of #CALC!.

Watch out: Without the $ signs, each copy shifts the export ranges down one row, so lower rows search a list that starts later and silently miss invoices near the top.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

FILTER decides which invoices belong to the supplier, TEXTJOIN decides how they are written, and the array passes from one to the other without ever touching the sheet. Absolute references on the export and a relative reference on the relation code are what let one formula serve every supplier in the list.

More to watch out for

  • A relation code exported as "C2001 " with a trailing space matches nothing; that invoice is missing from the cell without any error. If the number of invoices looks low, compare with COUNTIF.
  • The invoice numbers come out in export order, not sorted. Wrap the FILTER in SORT if you tick against a sorted statement.

Variations

=TEXTJOIN(", ", TRUE, IF($B$2:$B$9=D2, $A$2:$A$9, ""))
=TEXTJOIN(", "; TRUE; IF($B$2:$B$9=D2; $A$2:$A$9; ""))

For Excel 2019, which has TEXTJOIN but not FILTER: IF returns an invoice number or an empty text per row, and ignore_empty drops the empty ones. Confirm it with Ctrl+Shift+Enter in that version.

MasterExcel 2024 or later

A sorted, distinct list with 'and' before the last item

For the journal entry test you write one sentence per ledger account in the work paper: 'Account 4500 was posted from BNK, INK and MEM.' The export repeats journals, and one line has no journal code.

The sentence must:

  • name each journal once
  • list the journals alphabetically
  • use commas and 'and' before the last one
  • not break for an account with one journal or none
ABCDEF
1AccountJournalAccount45004500
245004500INKJournalsBNK, INK and MEM=LET( items, SORT(UNIQUE(FILTER(B2:B9, (A2:A9=E1)*(B2:B9<>""), ""))), n, ROWS(items), IF(n = 1, items, TEXTJOIN(", ", TRUE, DROP(items, -1)) & " and " & TAKE(items, -1)) )=LET( items; SORT(UNIQUE(FILTER(B2:B9; (A2:A9=E1)*(B2:B9<>""); ""))); n; ROWS(items); IF(n = 1; items; TEXTJOIN(", "; TRUE; DROP(items; -1)) & " and " & TAKE(items; -1)) )
380008000VRK
445004500BNK
545004500
680008000MEM
745004500INK
845004500MEM
911001100BNK
10
Formula cell (E2)Input

Formula in E2

=LET(
  items, SORT(UNIQUE(FILTER(B2:B9, (A2:A9=E1)*(B2:B9<>""), ""))),
  n, ROWS(items),
  IF(n = 1, items, TEXTJOIN(", ", TRUE, DROP(items, -1)) & " and " & TAKE(items, -1))
)
=LET(
  items; SORT(UNIQUE(FILTER(B2:B9; (A2:A9=E1)*(B2:B9<>""); "")));
  n; ROWS(items);
  IF(n = 1; items; TEXTJOIN(", "; TRUE; DROP(items; -1)) & " and " & TAKE(items; -1))
)

Result: BNK, INK and MEM

Step by step
  1. FILTER keeps rows where the account is 4500 and the journal is not empty: 1 × 1 for rows 2, 4, 7 and 8 of the sheet, while row 5 fails the second test. It returns INK, BNK, INK, MEM.
  2. UNIQUE removes the second INK: INK, BNK, MEM. SORT orders them: BNK, INK, MEM. That array is items.
  3. n is ROWS(items): 3. That is not 1, so IF takes the second branch.
  4. DROP(items, -1) removes the last item, leaving BNK and INK, and TEXTJOIN joins them: BNK, INK. TAKE(items, -1) keeps only the last item: MEM.
  5. The & operator glues the three parts: 'BNK, INK' & ' and ' & 'MEM' = BNK, INK and MEM.
  6. Type 8000 in E1: two journals, VRK and MEM, give MEM and VRK. Type 1100: one journal, n is 1, and E2 shows BNK. Type an account with no rows: FILTER returns its empty text, n is 1, and E2 shows an empty text instead of an error.

Watch out: The mirror-image mistake is the delimiter array. TEXTJOIN({", "," and "}, TRUE, items) uses the delimiters in turn and starts again from the first, so for three items it produces exactly BNK, INK and MEM and looks right.

More on this example: why it works, 3 more pitfalls, 1 variation

Why it works

TEXTJOIN has one delimiter for every gap, so a sentence that needs a different word before the last item has to be built in two parts: everything but the last, joined with commas, then the connector, then the last item. LET computes the cleaned list once and reuses it three times, and the n = 1 branch covers the single-item and empty cases, where DROP would otherwise fail.

Do not use this construction when the list is long or will be processed further: a sentence of 200 codes is unreadable, and nothing can count or sort a joined text. Keep a spilled column with SORT(UNIQUE(FILTER(...))) instead; it takes more rows but stays usable for formulas and pivot tables.

Watch out for

  • The mirror-image mistake is the delimiter array. TEXTJOIN({", "," and "}, TRUE, items) uses the delimiters in turn and starts again from the first, so for three items it produces exactly BNK, INK and MEM and looks right.

    With four items (BNK, INK, MEM, VRK) it gives BNK, INK and MEM, VRK: the 'and' lands in the wrong place.

  • Leaving out the B2:B9<>"" test lets the line without a journal through; UNIQUE turns it into a 0 that SORT puts first, and the sentence says 'Account 4500 was posted from 0, BNK, INK and MEM'.
  • Account numbers that the export delivers as text in some rows never equal the number 4500 in E1, so those lines silently drop out of the sentence. Convert the account column to numbers first, or compare with VALUE.

Variations

=TEXTJOIN(", ", TRUE, SORT(UNIQUE(FILTER(B2:B9, (A2:A9=E1)*(B2:B9<>""), ""))))
=TEXTJOIN(", "; TRUE; SORT(UNIQUE(FILTER(B2:B9; (A2:A9=E1)*(B2:B9<>""); ""))))

Commas only, no 'and': one line, and enough for an internal list where grammar does not matter.

Common mistakes

SymptomCauseFix
Two separators in a row in the resultThe result reads 'BNK / 25-0577 / / 2025-1088', with two slashes in a row.ignore_empty is FALSE and one of the cells is empty. Set the second argument to TRUE.
A date appears as 46296A date in the list shows up as 46296.TEXTJOIN uses the stored value, which is a serial number. Wrap the date in TEXT with a date format code first.
#VALUE! on a long listA long list of comments joined into one cell returns #VALUE!.The joined text is longer than 32,767 characters, the most a cell can hold. Join fewer items, or keep them as a spilled list.
Wrong result or error from the argument orderThe formula is written as TEXTJOIN(A2:A4, ", ", TRUE) and the result is wrong or an error.The separator comes first, then TRUE or FALSE, then the texts. Read it as 'join with this, skipping blanks or not, these items'.

Used in combinations