How to Split Delimited Text and Create a Unique List in Excel
Question details
Extract and split comma-separated values from multiple cells or a table column into a single column, while removing duplicates to create a clean, unique list.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Processing raw data where multiple items are packed into single cells separated by commas, needing to be consolidated into a single deduplicated column.
- Observed behavior
- The user wants to dynamically generate a clean, unique column list using formulas instead of relying on Power Query or manual text-to-columns tools.
Ensure your version of Excel or spreadsheet software supports dynamic array functions such as UNIQUE, TEXTSPLIT, and REDUCE before applying these formulas.
Extract a Unique List from a Standard Cell Range
Use a combination of REDUCE, VSTACK, and TEXTSPLIT to iterate through a standard range, split the delimited text, and remove duplicates.
This method is ideal when your comma-separated values are located in a standard, unstructured cell range. By leveraging the LAMBDA function within REDUCE, the formula sequentially splits each cell's contents and stacks them into a single column array before extracting the unique values.
Determine the cell range containing your comma-separated text (for example, D3:D6).
Click on the blank cell where you want the unique list to begin (e.g., G2).
Type the following formula: =UNIQUE(TOCOL(DROP(REDUCE("",D3:D6,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,",")))),1),2))
Press Enter. The formula will automatically spill the deduplicated list down the column.
Split and Deduplicate Text from an Excel Table
Use TEXTJOIN combined with TEXTSPLIT for a streamlined formula when your source data is formatted as an Excel Table.
Easily Split and Deduplicate Text using WPS Spreadsheet
WPS Spreadsheet fully supports modern dynamic array formulas, allowing you to split delimited strings, stack arrays, and extract unique values without needing complex macros. It provides a seamless, free alternative for advanced data processing tasks.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the file containing your comma-separated text data.
- 2. Select the target output cell: Click the blank cell where you want the new unique list to spill into.
- 3. Apply the dynamic formula: Paste the UNIQUE and TEXTSPLIT formula referencing your data range, and press Enter to instantly generate the clean list.
Frequently Asked Questions
Can I use a different delimiter instead of a comma?
Yes, you can easily modify the delimiter argument in the TEXTSPLIT function. For example, if your text is separated by semicolons, change the "," in the formula to ";".
Why does my formula return a #NAME? error?
A #NAME? error typically indicates that your current version of the spreadsheet software does not support dynamic array functions like TEXTSPLIT or LAMBDA. Ensure you are using a modern version of Excel or WPS Office.
How do I sort the resulting unique list alphabetically?
You can wrap your entire formula in the SORT function. For example, formatting your formula as =SORT(UNIQUE(...)) will return the final unique array in ascending alphabetical order.
