When preparing summary executive dashboards, email distribution lists, or database import files in Google Sheets, analysts frequently need to combine multiple rows of data into a single, delimited text cell (e.g., creating a comma-separated list of attendees, tags, or product categories). Using basic string concatenation or classic JOIN formulas captures duplicate entries and blank cells, producing cluttered strings with repeated words and double commas. By combining TEXTJOIN with UNIQUE and FILTER, you can create clean, deduplicated, and formatted delimited strings with a single formula.
Understanding the Syntax of TEXTJOIN and UNIQUE
Let us examine how each core function works individually:
1. The TEXTJOIN Function
=TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])Combines text elements together using a designated delimiter (such as a comma and space ", "), with a boolean flag allowing automatic omission of blank cells.
2. The UNIQUE Function
=UNIQUE(range, [by_column], [exactly_once])Returns an array containing only the distinct, non-duplicated values from the provided range.
Combining TEXTJOIN and UNIQUE
To consolidate a column containing repetitive entries into a single, comma-separated list of distinct values, nest UNIQUE directly inside TEXTJOIN:
=TEXTJOIN(", ", TRUE, UNIQUE(A2:A50))How this evaluation flows:
UNIQUE(A2:A50)filters out duplicate rows in memory, leaving an array of distinct items.TEXTJOIN(", ", TRUE, ...)joins those distinct items together with a comma and space, automatically ignoring any empty cells.- The consolidated string outputs cleanly inside a single target cell.
Adding Conditional Filtering with FILTER
In real-world business sheets, you often need to aggregate distinct items based on a specific category or status. For instance, suppose you have a client ledger where Column A lists Department, and Column B lists Assigned Software Tools. To generate a comma-separated list of unique software tools assigned strictly to the “Marketing” department:
=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(B2:B100, A2:A100 = "Marketing")))This formula first isolates tools belonging to Marketing, strips any duplicate tool assignments, and merges the result into a clean summary string.
Alphabetical Sorting with SORT
To ensure your output appears structured and professional, wrap the unique values in the SORT function before joining:
=TEXTJOIN("; ", TRUE, SORT(UNIQUE(A2:A50)))The resulting string lists every unique entry in clean alphabetical order separated by semicolons.
Formula Comparison for Text Aggregation
| Formula Combination | Handles Duplicates? | Handles Empty Cells? | Alphabetical Order? |
|---|---|---|---|
JOIN(", ", A2:A20) |
No (Includes duplicates) | No (Produces “, , “) | No |
TEXTJOIN(", ", TRUE, A2:A20) |
No (Includes duplicates) | Yes (Skips blanks) | No |
TEXTJOIN + UNIQUE |
Yes (Deduplicates) | Yes (Skips blanks) | No |
TEXTJOIN + SORT + UNIQUE |
Yes (Deduplicates) | Yes (Skips blanks) | Yes (Sorted A–Z) |
Combining TEXTJOIN and UNIQUE in Google Sheets simplifies data reporting, allowing users to build polished executive summaries and clean email lists without manual editing.