Yes. In Microsoft Excel for Microsoft 365, combine TEXTJOIN with UNIQUE to put distinct values from a range into one cell: enter =TEXTJOIN(", ",TRUE,UNIQUE(A2:A8)). The result joins each different value once, in the order it first appears.
What TEXTJOIN and UNIQUE do together
UNIQUE identifies distinct entries in a range, while TEXTJOIN combines a series of values into one text string using a delimiter you choose. Used together, they answer a common spreadsheet need: show a compact list of categories, names, or labels without repeating duplicates.
This is different from simply joining a range. A formula such as =TEXTJOIN(", ",TRUE,A2:A8) includes repeated entries; adding UNIQUE removes repeats before the join happens. The combined formula returns one result in the cell where you enter it. It does not spill the individual distinct values into neighboring cells.
For example, suppose A2:A8 contains Red, Blue, Red, Green, Blue, Yellow, and Green. With those values in A2:A8, =TEXTJOIN(", ",TRUE,UNIQUE(A2:A8)) returns Red, Blue, Green, Yellow. Each distinct entry appears once, and the order follows its first occurrence in the source range.
Build the basic formula
Enter the formula in the cell where you want the finished list. The example uses a comma and a space as the separator; change that text if you want a different appearance. For instance, a semicolon separator can be used for a regional list, while a line break can make a taller, multiline result.
- Put the source values in one range, such as A2:A8.
- Select the output cell and enter
=TEXTJOIN(", ",TRUE,UNIQUE(A2:A8)). - Press Enter. Excel displays the joined unique values in that cell.
The first argument, ", ", is the delimiter. The second argument, TRUE, tells TEXTJOIN to ignore empty items. The final argument is the array returned by UNIQUE. Excel recalculates the result when the source data changes, so the joined text can update as entries are edited or added within the referenced range.
Remove blanks and choose the output order
Blank cells need care. Although TEXTJOIN can ignore empty text, a blank cell passed through an array formula may be represented in a way that appears as zero. A reliable approach is to filter blank source cells out before asking UNIQUE to remove duplicates.
With entries in A2:A10, use =TEXTJOIN(", ",TRUE,UNIQUE(FILTER(A2:A10,A2:A10<>""))). FILTER keeps only cells that are not empty; UNIQUE then removes repeats; TEXTJOIN combines the remaining values. This also makes the intent of the formula clear when other people inspect the workbook.
By default, UNIQUE preserves the first-seen order rather than alphabetizing the output. If an alphabetical list is preferable, place SORT around UNIQUE. With values in A2:A8, =TEXTJOIN(", ",TRUE,SORT(UNIQUE(A2:A8))) returns the distinct entries in sorted order before joining them. SORT follows Excel’s sorting behavior for the values and data types involved, so mixed text and numbers may not behave like a manually curated category order.
Keep the order choice in mind when the sequence itself carries meaning. A list of stages such as Intake, Review, and Approved may be better in source order than alphabetically. For a set of product names or locations, sorting can make the final text easier to scan.
Join unique values that meet a condition
You can combine the functions with FILTER to include only records that match a criterion. For example, suppose A2:A7 contains city names and B2:B7 contains regions. If the North rows contain Toronto, Ottawa, Toronto, and Montreal, the formula can return Toronto and Ottawa when the criterion is North.
With cities in A2:A7 and their regions in B2:B7, enter =TEXTJOIN(", ",TRUE,UNIQUE(FILTER(A2:A7,B2:B7="North"))). FILTER selects city names from rows whose region is North, and UNIQUE removes repeated city names before the join. The result for the example is Toronto, Ottawa, following the first-seen order among the matching rows.
For a condition that might have no matches, provide FILTER’s optional empty-result argument. With the same layout, =TEXTJOIN(", ",TRUE,UNIQUE(FILTER(A2:A7,B2:B7="West",""))) uses an empty string when there are no West rows, rather than allowing FILTER’s no-results error to flow into the rest of the formula. If the condition is based on a cell, replace the quoted criterion with a cell reference such as D1.
Understand the function options
UNIQUE has the syntax UNIQUE(array,[by_col],[exactly_once]). The optional by_col argument controls whether Excel compares columns rather than rows; it is usually left out when working with a single vertical list. The optional exactly_once argument changes the meaning of “unique.” When TRUE, it returns only values that occur exactly once in the source data.
That distinction matters. The normal formula keeps one copy of every distinct value, even if it appears many times. Setting exactly_once to TRUE instead excludes values that repeat. With values in A2:A8, =TEXTJOIN(", ",TRUE,UNIQUE(A2:A8,FALSE,TRUE)) joins only entries appearing once. For the earlier Red, Blue, Red, Green, Blue, Yellow, Green example, the result is Yellow, not a list containing Red and Blue.
Excel’s text comparisons for functions such as UNIQUE are generally not case-sensitive, so capitalization alone should not be treated as a dependable way to distinguish categories. If north and North are intended to be separate labels, normalize or otherwise explicitly handle the data before relying on a distinct-value list.
Version and compatibility notes
TEXTJOIN is available in modern Excel editions, but UNIQUE is the version-dependent part of the combined formula. UNIQUE is available in Microsoft 365 and Excel 2021 or later, including Excel 2024. In older Excel releases without UNIQUE, the formula will not work as written. Microsoft Excel for the web also supports dynamic-array formulas, and Microsoft 365 for Mac includes these functions; ribbon locations and some keyboard shortcuts differ from Windows, but the formula syntax is the same.
In some installations, Excel uses semicolons instead of commas between formula arguments because of regional settings. If Excel rejects a formula copied from a comma-based example, check the list separator configured for your locale. This is separate from the delimiter inside quotation marks: the comma in ", " is text that appears in the result, not an argument separator.
Dynamic-array functions can also show a compatibility warning when a workbook is opened in an older Excel version. A user without UNIQUE may see an unsupported-function error rather than the calculated list. If the workbook must be shared with such users, consider creating a static copy of the final text or using a compatible data-preparation method.
Common errors and practical limits
- #NAME? often indicates that the Excel version does not recognize UNIQUE, or that a function name was misspelled. Confirm that the workbook is opened in a supported version.
- #CALC! can occur when FILTER finds no matching rows and no empty-result value was supplied. Add FILTER’s third argument, as shown above.
- #VALUE! can occur if the final joined text exceeds Excel’s cell text limit of 32,767 characters. A single cell cannot hold an arbitrarily long list.
- Unexpected zeroes or blanks can come from blank cells in the source range. Use FILTER to exclude empty cells, and check whether formulas in the range return an empty string.
- Unexpected duplicates or missing distinctions can result from inconsistent source data, such as trailing spaces, different spellings, or capitalization differences. Clean or standardize entries if they should represent the same category.
Also check that the referenced range covers all relevant rows. A formula pointing to A2:A20 will not include a new value entered in A21. You can use an Excel Table as the source to make ranges more adaptable as rows are added, or update the range reference when the data grows. If the source contains error values, those errors may affect the formula; resolve or deliberately handle them rather than assuming the join will skip them.
When to use another approach
TEXTJOIN with UNIQUE is a good fit when the goal is a readable, compact text result in one cell, such as a comma-separated list for a report or a summary field. It is less suitable when users need to filter, sort, or analyze each distinct item as a separate record. In that case, use =UNIQUE(A2:A8) in an output area and let the results spill into separate cells.
For a one-time cleanup, Excel’s Remove Duplicates command can remove repeated records from selected source data, but it changes the data rather than simply displaying a combined summary. Work on a copy or a separate range if the original records must be preserved. Power Query is another option for repeatable import-and-clean workflows, especially when the source data is large or needs several transformation steps. These approaches solve related problems, but neither is a replacement for TEXTJOIN when the required output is one text string.
Frequently Asked Questions
Can TEXTJOIN remove duplicates by itself?
No. TEXTJOIN joins the values it receives; it does not identify duplicates. Pair it with UNIQUE, as in =TEXTJOIN(", ",TRUE,UNIQUE(A2:A8)), to join each distinct value once.
How do I put each unique value on a new line?
Use a line-break character as the delimiter. With values in A2:A8, enter =TEXTJOIN(CHAR(10),TRUE,UNIQUE(A2:A8)), then turn on Wrap Text for the output cell using Home > Wrap Text. The line breaks may not be visually obvious until wrapping is enabled.
How do I get unique values in alphabetical order?
Wrap UNIQUE in SORT. With values in A2:A8, =TEXTJOIN(", ",TRUE,SORT(UNIQUE(A2:A8))) sorts the distinct values before joining them. Remove SORT if the original first-seen order is more useful.
Can I return only values that occur once?
Yes. The third argument of UNIQUE can be TRUE. With values in A2:A10, =TEXTJOIN(", ",TRUE,UNIQUE(A2:A10,FALSE,TRUE)) returns only entries whose count in that range is exactly one. This differs from the default, which keeps one copy of any value that appears at least once.
Why does UNIQUE not work in my Excel?
The function requires Microsoft 365, Excel 2021, or a later Excel edition such as Excel 2024. Older releases may not recognize it. If a workbook is being shared across versions, use an available alternative or provide the joined output as static text for recipients who cannot calculate the formula.
Related
- Python Pandas Excel Export: Methods and Examples
- CSV File to Excel Converter: Convert Without Data Loss
- Data Cleaning Tools in Excel: What to Use and When
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Tomorrow’s Date: TODAY()+1 ExplainedIn Excel, use =TODAY()+1 to return tomorrow’s date. Enter it in a cell and format that cell as…
- Excel MATCH: Find, Filter, and Export Matching DataExcel’s MATCH function does not export data: it finds the position of a value in a range. To…
- Excel Time Difference in Minutes: Formulas and TipsTo calculate the minutes between a start time in A2 and an end time in B2, enter =(B2-A2)*1440…
- Excel MID Function: Can It Return Variable-Length Text?Yes. Excel’s MID function can return a variable number of characters when you calculate its num_chars argument from…
- Outlook Rules: Filter Your Incoming Mail AutomaticallyCreate an Outlook rule by choosing a condition, such as who sent a message or a word in…
- Conditional Formatting Dates in Excel: Rules and FormulasTo conditionally format dates in Excel for Microsoft 365 on Windows, select the date cells and choose Home…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.