Text Join and Filter in Excel: Formulas and Examples

Formulas & functions · Updated November 20, 2026

With names in A2:A10 and their statuses in B2:B10, use =TEXTJOIN(", ",TRUE,FILTER(A2:A10,B2:B10="Open","No open items")) to return one comma-separated list of the names whose status is Open. FILTER selects the matching cells; TEXTJOIN combines those results into a single text value.

What text join and filter mean

“Text join and filter” usually means selecting only the values that meet a condition, then combining the selected values in one cell. It is useful when a worksheet has a long list but you need a compact summary, such as the names of open tasks, products in a category, or employees assigned to a shift.

Excel handles the two parts with separate functions. FILTER returns the rows or values that meet a logical test. TEXTJOIN takes text values and combines them with a delimiter you choose, such as a comma, semicolon, space, or line break. Together, they make a result that updates when the source data changes.

This is different from filtering a worksheet with the Data tab. A worksheet filter hides rows that do not meet a condition; it does not create a combined text result. The formula approach leaves the source list intact and displays a separate summary.

A basic example with sample data

Suppose A2:A6 contains task owners and B2:B6 contains their statuses:

  • Maya — Open
  • Leon — Closed
  • Priya — Open
  • Omar — Open
  • Zoe — Closed

With the full list in A2:A6 and the matching statuses in B2:B6, enter =TEXTJOIN(", ",TRUE,FILTER(A2:A6,B2:B6="Open","No open items")). The result is Maya, Priya, Omar. If one of the statuses changes, Excel recalculates the list automatically.

The formula has three layers. The text ", " is the delimiter placed between returned names. TRUE tells TEXTJOIN to ignore empty values. FILTER checks each cell in B2:B6 and returns the value from the corresponding position in A2:A6 only when the status is Open. The third FILTER argument supplies a message if no rows match.

How the functions work together

FILTER uses the pattern FILTER(array,include,[if_empty]). The array is what you want returned; include is a same-sized range or logical test that identifies the values to keep; and if_empty is an optional result for a search with no matches. The include test must line up with the array. If A2:A20 holds names, a status test in B2:B20 has the required matching number of rows.

TEXTJOIN uses the pattern TEXTJOIN(delimiter,ignore_empty,text1,[text2],…). Its first argument controls what appears between items, and its second controls whether empty values are skipped. It can combine a range or the array returned by FILTER. The result is text in one cell, not a new range of separate values.

The functions are non-destructive: they do not edit, sort, or delete the source data. The result is a formula output, so you normally cannot edit one of the joined names inside the output cell. Change the source data or the formula’s condition instead.

Changing the delimiter and matching condition

A comma and space are easy to scan, but other separators can suit different uses. With names in A2:A10 and matching statuses in B2:B10, use =TEXTJOIN("; ",TRUE,FILTER(A2:A10,B2:B10="Open","No open items")) to separate the selected names with semicolons. Choose a separator that will not be mistaken for part of the values if the result will later be split or imported elsewhere.

For a vertical-looking list inside one cell, use a line break as the delimiter. With names in A2:A10 and statuses in B2:B10, enter =TEXTJOIN(CHAR(10),TRUE,FILTER(A2:A10,B2:B10="Open","No open items")), then select the result cell and choose Home > Wrap Text. CHAR(10) supplies the line-feed character. Increase the row height if the full list is not visible.

Conditions need not be a single fixed word. For example, the condition can point to a cell containing the desired status, so a reader can change the selection without editing the formula. If the status to find is in D1, and names and statuses are in A2:A10 and B2:B10, use =TEXTJOIN(", ",TRUE,FILTER(A2:A10,B2:B10=D1,"No matches")). This makes the summary reusable for different status values.

Combining more than one condition

You can combine logical tests when the task requires multiple criteria. Multiplication acts like AND: both conditions must be true. Addition acts like OR: either condition can be true. In these formulas, TRUE and FALSE values are treated as 1 and 0 to form the include array.

Assume A2:A10 contains names, B2:B10 contains status, and C2:C10 contains priority. To return names that are both Open and Urgent, use =TEXTJOIN(", ",TRUE,FILTER(A2:A10,(B2:B10="Open")*(C2:C10="Urgent"),"No matches")). Parentheses make the separate tests clear. To return names that are Open or Urgent, change the multiplication sign between the tests to a plus sign: =TEXTJOIN(", ",TRUE,FILTER(A2:A10,(B2:B10="Open")+(C2:C10="Urgent"),"No matches")).

For a partial text match, SEARCH can find a word within a cell, and ISNUMBER turns a successful search into TRUE. With names in A2:A10 and notes in C2:C10, use =TEXTJOIN(", ",TRUE,FILTER(A2:A10,ISNUMBER(SEARCH("north",C2:C10)),"No matches")) to return names whose notes contain “north.” SEARCH is not case-sensitive. If the search term is in D1, replace the quoted word with D1 to make the search adjustable.

Versions, entry, and compatibility

FILTER is available in Microsoft 365 and Excel 2021 or later, as well as supported versions of Excel for the web. TEXTJOIN is available in Microsoft 365 and recent desktop versions, including Excel 2019 and later. If a workbook containing FILTER is opened in an older Excel version that does not support it, the formula may not calculate there as intended.

In Microsoft 365 for Windows, select the cell for the result, type the formula in the formula bar or directly in the cell, and press Enter. FILTER is a dynamic-array function: it can return multiple values to neighboring cells by itself, but TEXTJOIN consumes that array and returns a single result. No special Ctrl+Shift+Enter entry is needed for this combination.

The formula syntax shown uses commas, as in standard English-language Excel settings. Some regional configurations use semicolons between function arguments instead. On Mac, the function names and basic formula behavior are the same; the available interface and keyboard shortcuts can differ. Excel for the web supports these functions in current supported accounts, although some desktop ribbon commands and controls may be arranged differently.

Common problems and practical fixes

  • No matches: FILTER returns the optional if_empty value when supplied. Without it, a no-match result can be a #CALC! error. Check spelling, extra spaces, and whether the condition refers to the intended cells.
  • Different range sizes: The FILTER array and include test need corresponding dimensions. For example, a ten-row list should not be paired with a nine-row condition range. Align the start and end rows.
  • Unexpected blanks: TRUE in TEXTJOIN skips genuinely empty values in the returned array. It does not remove spaces from cells that look blank, nor does it trim spaces from names. Clean the source values if unwanted spaces remain.
  • Numbers and dates look different: Joining values produces text, and a date or number may not retain the display format you expect. For dates, format the value explicitly with TEXT before joining if a particular date style is important. Be aware that the format code in TEXT can depend on regional settings.
  • Long result: A cell can hold at most 32,767 characters. A very large joined list can exceed that limit and produce an error. Consider returning the matching values in separate cells with FILTER, or summarize them in a different way.
  • Formula displays instead of calculating: Check that the cell is not formatted as Text, that the formula begins with an equals sign, and that Show Formulas is not turned on. Re-entering the formula after changing a Text-formatted cell to General can help.

TEXTJOIN does not remove duplicates. If the source list includes the same name several times, each matching occurrence can appear in the combined output. If the goal is a unique list, Excel 365 or Excel 2021 can use UNIQUE with FILTER before joining; make sure the additional function is supported in the workbook’s target version.

How it compares with other Excel options

CONCAT joins text but does not provide TEXTJOIN’s built-in delimiter and ignore-empty arguments. The older CONCATENATE function can join text too, but it is less convenient for ranges. TEXTJOIN is generally the clearest choice when a delimiter is needed, while FILTER is the part that selects records based on criteria. Neither function is a replacement for a worksheet filter when the aim is to inspect or edit complete rows.

If you want the matching entries displayed one per row rather than in one cell, use FILTER on its own. That can be easier to sort, count, or reference in later calculations. If you need a repeatable data-cleaning process across many files, Power Query may be more suitable than a long formula. It can filter and transform imported data, but it is a separate workflow and does not produce the same immediately recalculating joined-text cell.

For an older Excel version without FILTER, a helper column can mark rows that meet the condition, followed by a compatible way to combine the marked entries. The exact approach depends on the version and whether array formulas are supported. If a workbook must work across mixed versions, test it in the oldest version readers will use rather than assuming a dynamic-array formula will translate.

Frequently Asked Questions

Can I use TEXTJOIN and FILTER in one Excel formula?
Yes. FILTER supplies the matching values as an array, and TEXTJOIN combines them with the chosen delimiter. The formula returns one text string in the cell where you enter it.

How do I return an empty cell when there are no matches?
Set FILTER’s optional if_empty argument to an empty text string, written as two quotation marks with nothing between them. Alternatively, use a clear message such as “No matches” so that an empty result is not confused with a formula problem.

Does TEXTJOIN remove duplicate results?
No. It joins each value FILTER returns, including repeated values. Use UNIQUE as part of the calculation if you want distinct values, and confirm that the workbook’s Excel version supports UNIQUE.

Can I join matching values with line breaks?
Yes. Use CHAR(10) as the TEXTJOIN delimiter and turn on Home > Wrap Text for the output cell. You may also need to adjust the row height to see every line.

Why does Excel show #NAME? for FILTER?
The Excel version may not support FILTER, or the function name may be misspelled. FILTER is available in Microsoft 365 and Excel 2021 or later; in older editions, use a compatible alternative or open the workbook in a supported version.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.