SheetSense

TEXTJOIN with a delimiter and a condition

TEXTJOIN glues many cells into one piece of text with a separator you choose, and with FILTER inside it joins only the rows that meet a condition.

fx
=IFERROR(TEXTJOIN(", ",TRUE,FILTER($B$2:$B$100,$A$2:$A$100=E2)),"")

The sheet: Tasks sheet: column A is Project, column B is Owner. Cell E2 holds a project name, and you want every owner on that project in one cell.

How it works, step by step

  1. 01FILTER($B$2:$B$100,$A$2:$A$100=E2) returns the owners on rows where the project equals the value in E2.
  2. 02TEXTJOIN(", ",TRUE,...) joins those owners into one text string, putting a comma and a space between them.
  3. 03The TRUE in the middle tells TEXTJOIN to skip empty cells, so you do not get stray separators.
  4. 04IFERROR returns an empty string when FILTER finds no rows, instead of showing #N/A.

The same logic in four tools

Excel
=IFERROR(TEXTJOIN(", ",TRUE,FILTER($B$2:$B$100,$A$2:$A$100=E2)),"")
Google Sheets
=IFERROR(TEXTJOIN(", ",TRUE,FILTER($B$2:$B$100,$A$2:$A$100=E2)),"")
SQL
-- PostgreSQL. MySQL uses GROUP_CONCAT(owner ORDER BY task_id SEPARATOR ', ').
SELECT project,
       STRING_AGG(owner, ', ' ORDER BY task_id) AS owners
FROM tasks
WHERE owner IS NOT NULL
GROUP BY project;
Python (pandas)
import pandas as pd

owners = (
    tasks.dropna(subset=["owner"])
    .groupby("project")["owner"]
    .agg(", ".join)
)

Mistakes that break it

  • !TEXTJOIN needs Excel 2019 or Microsoft 365. FILTER inside it needs Excel 2021 or 365. In Excel 2019 use TEXTJOIN(", ",TRUE,IF($A$2:$A$100=E2,$B$2:$B$100,"")) and confirm with Ctrl+Shift+Enter.
  • !A single cell holds at most 32,767 characters. A very long list is cut off or returns #VALUE!.
  • !Passing FALSE as the second argument keeps blank cells, which adds empty items such as "Ana, , Ben".
  • !SQL returns rows in no guaranteed order unless you add ORDER BY inside STRING_AGG. Without it the owners may not appear in sheet order.

Common questions

What is the difference between TEXTJOIN and CONCAT?
CONCAT joins values with nothing between them. TEXTJOIN adds a delimiter you choose between every value and can skip empty cells.
How do I join values with a line break?
Use CHAR(10) as the delimiter, =TEXTJOIN(CHAR(10),TRUE,B2:B6), and turn on Wrap Text for the cell.
What is the SQL version of TEXTJOIN?
STRING_AGG(column, ', ') with GROUP BY in PostgreSQL and SQL Server, and GROUP_CONCAT in MySQL and SQLite.

Got a version of this with your own ranges?

Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.