TEXTJOIN Generator
Combine a range of cells into one string with your chosen delimiter, skipping blanks if you want.
TEXTJOIN
Combine values from a range into one string with a delimiter.
Text placed between each value. Needs quotes, for example ", ".
TRUE skips blank cells; FALSE keeps them.
The cells whose values you want to join.
=TEXTJOIN(", ", TRUE, A2:A10)Checks Before You Paste
- •Use CHAR(10) as the delimiter to join onto separate lines.
- •TEXTJOIN needs Excel 2019+ or Microsoft 365; use CONCATENATE otherwise.
- •Set ignore-empty to TRUE to avoid doubled-up delimiters.
How TEXTJOIN works
Availability: Excel 2019, Microsoft 365, Excel for Mac 2019, Excel for the web and Google Sheets. Excel 2016 and earlier do not have it and show #NAME?; CONCATENATE or & is the fallback there.
TEXTJOIN puts a delimiter between values and joins them into one string. What separates it from CONCATENATE and from the & operator is that it accepts whole ranges rather than one argument per cell, and that it can skip empties. =TEXTJOIN(", ", TRUE, A2:A50) is one short formula where CONCAT or & would need 49 references and would still leave a trail of stray commas wherever a cell was blank.
The ignore_empty flag is the argument that earns the function. Set to TRUE, blank cells and zero-length strings are dropped before the delimiters are inserted, so a five-cell range with two blanks produces three values and two separators. Set to FALSE, every cell contributes its position, so the same range gives "Red,,Blue,,Green" - which is what you want when the output is being parsed back into fixed columns later, and almost never otherwise.
The delimiter does not have to be a single character or even a single value. Pass an array and TEXTJOIN cycles through it, so =TEXTJOIN({": ","; "}, TRUE, A2:A7) alternates the two separators - useful for key/value pairs pulled from a two-column range read row by row. CHAR(10) as the delimiter puts each value on its own line, though the cell must have Wrap Text switched on before the line breaks become visible; on Mac, CHAR(13) is the older equivalent.
Everything TEXTJOIN returns is text, including numbers and dates, and it uses the underlying value rather than the displayed one. A date shows up as its serial number, 46023 rather than 01/01/2026, and a currency cell loses its formatting. Wrap the values in TEXT with an explicit format code when the output is for people to read. The result also has a hard ceiling of 32,767 characters, the maximum a cell can hold; going past it returns #VALUE!.
Syntax
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)| Argument | Required | What it does |
|---|---|---|
delimiter | Required | The text placed between values. "" joins with nothing. An array such as {", "," and "} is cycled through in order rather than repeated. |
ignore_empty | Required | TRUE drops blank cells and zero-length strings before joining. FALSE keeps their positions, producing consecutive delimiters. |
text1 | Required | The first value, range or array to join. A multi-column range is read left to right along each row in turn. |
text2 | Optional | Up to 252 further values or ranges, joined in the order given. They do not need to be adjacent or the same shape. |
More worked examples
A contact record spread across columns - title in A2, first name B2, middle name C2 (often blank) and surname D2.
=TEXTJOIN(" ", TRUE, A2:D2)Returns: "Dr Priya Anand" when C2 is empty, and "Dr Priya Lakshmi Anand" when it is not.
With ignore_empty set to FALSE the same row would return "Dr Priya Anand" with a double space, which is exactly the kind of defect that survives all the way into a mail merge.
Order lines for a single customer in B2:B40, and you want them listed one per line inside a single cell.
=TEXTJOIN(CHAR(10), TRUE, B2:B40)Returns: Every non-blank order reference stacked on its own line in one cell.
The line breaks are in the value straight away, but the cell shows one long line until Wrap Text is turned on for it.
Building a comma-separated list of only the overdue invoice numbers, with numbers in A2:A200 and status in C2:C200.
=TEXTJOIN(", ", TRUE, IF(C2:C200="Overdue", A2:A200, ""))Returns: "INV-1042, INV-1067, INV-1120" - only the rows whose status is Overdue.
IF over a range makes this an array formula. It works as typed in Excel 2021 and Microsoft 365; in Excel 2019 confirm it with Ctrl+Shift+Enter.
Common mistakes
- Dates and currency arriving as raw numbers
- TEXTJOIN reads stored values, not the formatting on top of them, so 01/01/2026 joins as 46023 and £1,250.00 as 1250. Format them first: TEXTJOIN(", ", TRUE, TEXT(A2:A20, "dd/mm/yyyy")).
- Expecting FALSE and TRUE to give the same delimiter count
- ignore_empty FALSE inserts a delimiter for every cell including the empty ones, so a range with gaps produces "A,,,B". If the output is being read by a person, TRUE is nearly always what you want; if it is being split back into columns later, FALSE preserves the positions.
- Line breaks that do not appear
- CHAR(10) is in the cell value immediately, but Excel only draws it once Wrap Text is enabled on that cell, and row height may need adjusting too. Nothing is wrong with the formula - check the alignment settings before rewriting it.
- Hitting the 32,767 character limit
- Joining a long column can exceed what one cell holds, and TEXTJOIN then returns #VALUE!. Split the source range across two or three formulas, or filter the range down before joining.
- Sharing the file with Excel 2016
- TEXTJOIN is stored prefixed as _xlfn.TEXTJOIN. Opened in Excel 2016 or earlier the cell shows #NAME? even though the formula is intact. For a workbook that has to travel to older builds, join with & or CONCATENATE and accept the extra references.
Frequently Asked Questions
CONCATENATE is the legacy function and takes individual values only. CONCAT (2019 and later) accepts ranges but has no delimiter and no way to skip blanks. TEXTJOIN accepts ranges, inserts a delimiter between values and can drop empties - it is the only one of the three that produces a usable list from a column with gaps.
Yes, by feeding it an IF over the range: =TEXTJOIN(", ", TRUE, IF(B2:B100="West", A2:A100, "")). The IF turns non-matching rows into zero-length strings and ignore_empty removes them. In Excel 2021 and 365 you can also pass FILTER(A2:A100, B2:B100="West") instead, which is faster on large ranges.
There is no argument for it. Join everything except the last value, then append it: =TEXTJOIN(", ", TRUE, A2:A9)&" and "&A10. The array-delimiter trick cycles separators by position and cannot single out the final one.
Only if the cell really contains text. A code stored as the number 00421 is the number 421, and that is what joins. Store such codes as text, or wrap them: TEXT(A2, "00000").
252 text arguments, each of which may itself be a whole range, so the practical limit is the 32,767-character result rather than the argument count. The arguments are joined strictly in the order you list them.