'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Combine Unique Values from Multiple Cells with Commas in Excel
Question details
The user needs to merge values from multiple cells into one single cell, separate them with commas, and automatically remove any duplicate numbers or text.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating lists or arrays of data that contain duplicate entries into a clean, comma-separated text string within a single cell.
- Observed behavior
- Users require a formula that accurately separates items with a comma, ignores empty cells, and outputs only the unique values from the referenced range.
Ensure your spreadsheet software supports dynamic array functions. The UNIQUE function is available in Microsoft 365, Excel 2021, and newer versions, as well as modern alternatives like WPS Spreadsheet.
Use TEXTJOIN and UNIQUE Functions
Combine the TEXTJOIN function with the UNIQUE function to automatically filter duplicates and merge the remaining values with a comma delimiter.
This method nests the UNIQUE function inside TEXTJOIN. The UNIQUE function first extracts a list of distinct values from your specified range, and then TEXTJOIN merges them together using your chosen delimiter (a comma and a space).
Click on the empty cell where you want the final comma-separated text to be displayed.
If your data is stored in a column (e.g., A1 to A10), type the following formula: =TEXTJOIN(", ", TRUE, UNIQUE(A1:A10))
If your data is stored across a row (e.g., A1 to C1), type the following formula: =TEXTJOIN(", ", TRUE, UNIQUE(A1:C1))
Press the Enter key. Carefully check that all parentheses and commas are placed correctly to prevent formula syntax errors.
Easily Combine Unique Cell Values in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like TEXTJOIN and UNIQUE, allowing you to seamlessly extract and merge data without complicated workarounds or macros.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing the data you want to merge.
- 2. Select the target cell: Click on the empty cell where you want the combined list to appear.
- 3. Apply the nested formula: Enter =TEXTJOIN(", ", TRUE, UNIQUE(A1:A10)) and press Enter to instantly view your comma-separated unique values.
Frequently Asked Questions
Why does my formula result in a #NAME? error?
The #NAME? error usually occurs if your version of Excel does not support the UNIQUE function (e.g., Excel 2019 or older). You need a version that supports dynamic arrays, such as Microsoft 365, Excel 2021, or WPS Office.
Why is the formula not working or returning a syntax error message?
Syntax errors often happen due to a missing comma between the function arguments. Double-check your formula to ensure it looks exactly like =TEXTJOIN(", ", TRUE, UNIQUE(A1:A10)), paying close attention to the commas separating the delimiter, the ignore_empty argument, and the UNIQUE function.
Will this formula work if my numbers are stored as text?
Yes. If your numbers are stored as text, the UNIQUE function will still evaluate and filter them accurately, and TEXTJOIN will successfully combine them, provided the cells contain the intended exact values.
