Extract Unique Characters from Text in WPS Spreadsheets with MID and UNIQUE
Question details
Extract characters 7 through 10 from 5,000 text values and display only the unique extracted results on a different worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The source text is stored in Sheet1 cells A1:A5000, and the user wants a de-duplicated list on a destination worksheet.
- Observed behavior
- The desired result is a spilled list containing each distinct four-character segment extracted from position 7 in the source values.
Confirm that the destination cells below and beside the formula cell are empty so the dynamic-array results can spill. Check that the source values are text strings with at least 10 characters when you need a complete four-character result.
Use UNIQUE with MID to return distinct extracted character groups
Use this one-cell dynamic-array formula when you need to extract four characters beginning at position 7 from every value and remove duplicate results automatically.
MID extracts a specified number of characters from each source value, while UNIQUE returns only one instance of every extracted result.
In WPS Spreadsheets, select the worksheet where you want the unique results to appear, then click the first destination cell, such as A1.
Type =UNIQUE(MID('Sheet1'!A1:A5000,7,4)) in the selected cell and press Enter.
WPS Spreadsheets fills the cells below the formula with the distinct four-character values. Do not type into the spill range; clear any blocking values if a spill error appears.
Create a unique extracted-text list with WPS Spreadsheets
WPS Spreadsheets lets you combine MID and UNIQUE in one dynamic-array formula to extract a fixed text segment from a large range and generate a distinct result list on another worksheet.
- 1. Open the source workbook: Open the workbook in WPS Spreadsheets and verify that the text values are in Sheet1 column A.
- 2. Choose an output cell: Go to the destination worksheet and select the top-left cell where the unique list should begin.
- 3. Apply the formula: Enter =UNIQUE(MID('Sheet1'!A1:A5000,7,4)) and press Enter.
- 4. Keep the spill range clear: Leave the cells beneath the formula available so WPS Spreadsheets can display all unique results.
Frequently Asked Questions
What does MID('Sheet1'!A1:A5000,7,4) return?
It returns four characters from each cell in Sheet1!A1:A5000, beginning with the seventh character in each text value.
Where should I enter the UNIQUE and MID formula?
Enter it in the first output cell on the destination worksheet, such as A1. The results spill into the cells below that cell.
Why does the formula show a spill error?
A spill error usually means one or more cells in the intended output range already contain data. Clear the cells below and around the formula cell, then recalculate.
Can I extract a different character position or length?
Yes. In MID(text,start_num,num_chars), change 7 to the character position where extraction should start and change 4 to the number of characters to return.
Will blank source cells create a blank item in the unique list?
They can. If the source range contains blanks, UNIQUE may return a blank result. Remove blank source values or use a FILTER condition if you need to exclude blanks.
