Fix TEXTSPLIT with UNIQUE in WPS Spreadsheets to Extract All Unique Values
Question details
The user wants to extract every unique value from delimiter-separated text stored across multiple cells by combining TEXTSPLIT with UNIQUE, without losing values after the first separator.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a range of cells containing delimiter-separated values and needs a formula to return a sorted list of all unique values.
- Observed behavior
- Applying TEXTSPLIT directly to a multi-cell range can process entries unexpectedly and omit text after the first separator unless the range is first combined into one string.
Confirm that your WPS Office version supports TEXTSPLIT, TEXTJOIN, UNIQUE, and SORT, and identify the delimiter used in your source cells, such as a slash.
Combine cells with TEXTJOIN before TEXTSPLIT and UNIQUE
Use this formula when you need a sorted, duplicate-free list from delimiter-separated values in a range.
TEXTSPLIT is designed to split a single text string, so passing a multi-cell range directly can produce incomplete results. TEXTJOIN merges the source cells into one delimiter-separated string, allowing TEXTSPLIT to process every value.
Click an empty cell where you want the spilled unique list to start, for example F3.
Type =SORT(UNIQUE(TEXTSPLIT(TEXTJOIN("/",TRUE,D3:D4),,"/",TRUE,0))) and adjust D3:D4 to your source range.
Replace "/" with the delimiter used in your data, such as "," or "-", in both TEXTJOIN and TEXTSPLIT.
Press Enter. WPS Spreadsheets spills the sorted unique values from the combined source cells.
Return unique values without sorting
Use this version if you want to keep the order in which values first appear in the source cells.
Extract all unique values with WPS Spreadsheets formulas
Use TEXTJOIN, TEXTSPLIT, UNIQUE, and SORT together in WPS Spreadsheets to turn multi-cell delimiter-separated data into a sorted unique list without losing values after the first separator.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the worksheet that contains the delimiter-separated source cells.
- 2. Select the output cell: Click an empty cell where the unique list should start, such as F3.
- 3. Enter the formula: Type =SORT(UNIQUE(TEXTSPLIT(TEXTJOIN("/",TRUE,D3:D4),,"/",TRUE,0))) and replace D3:D4 and "/" with your actual range and delimiter.
- 4. Press Enter: Press Enter to spill the sorted unique values automatically.
- 5. Save or share the workbook: Use File > Save As to keep the result in WPS format or save as an Excel .xlsx file for Microsoft Office compatibility.
Frequently Asked Questions
Why does TEXTSPLIT miss text after the first slash when I reference D3:D4 directly?
TEXTSPLIT expects one text string, so a multi-cell range can be interpreted in an unexpected way. Wrap the range in TEXTJOIN("/",TRUE,D3:D4) to merge all entries into a single string before splitting.
Can I use this technique with a comma-separated list instead of slashes?
Yes. Replace "/" with "," in both TEXTJOIN and TEXTSPLIT, for example: =SORT(UNIQUE(TEXTSPLIT(TEXTJOIN(",",TRUE,D3:D4),,",",TRUE,0))).
What do the TRUE and 0 arguments in TEXTSPLIT do?
TRUE tells TEXTSPLIT to ignore empty values produced by repeated delimiters, and 0 selects the default case-sensitive match mode in WPS Spreadsheets.
Do I have to include SORT in the formula?
No. If you prefer the order in which unique values first appear, use =UNIQUE(TEXTSPLIT(TEXTJOIN("/",TRUE,D3:D4),,"/",TRUE,0)) without SORT.
Is this formula available in WPS Spreadsheets?
Yes, recent WPS Office versions include TEXTJOIN, TEXTSPLIT, UNIQUE, and SORT. Update WPS Office if the functions are not recognized.
