WPS Spreadsheet: Search a Drop-Down List and Return the Selected Number
Question details
The user needs to create a name-and-number selection list and return the number associated with the selected entry.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A list contains entries such as “John Smith - 33346”. The user wants to select an entry from a drop-down list and extract the number after the hyphen.
- Observed behavior
- The desired numeric value is not yet extracted automatically from the selected drop-down entry.
Make sure every source entry uses the same separator, such as " - ", between the name and number. Place the complete list in a consistent cell range so it can be used as the data-validation source.
Create a drop-down list and extract the selected number
Use this method when each list item contains a name, a hyphen, and a number in the same cell.
The drop-down stores the full selected text in A2. A formula in B2 then finds the text after the hyphen and converts it to a number. Search behavior inside native drop-downs can vary by WPS or Excel version; for very large lists, use a Combo Box instead.
Enter entries such as "John Smith - 33346" and "James Smith - 33345" in a source range, for example D2:D20.
Select cell A2, open Data > Data Validation, choose List, and set the source to the range containing the names and numbers. Confirm the dialog to add the drop-down.
Select cell B2 and enter =IFERROR(--TEXTAFTER(A2," - "),""). Press Enter to return the numeric value after the hyphen.
Open the drop-down in A2 and select a different item. B2 updates automatically with the number associated with that selection.
Build searchable selection lists in WPS Spreadsheet
WPS Spreadsheet lets you create a Data Validation list, select a complete name-and-number entry, and use a formula to return the associated numeric value. Its familiar spreadsheet interface also makes it easy to maintain the source list and update selections.
- 1. Open WPS Spreadsheet: Launch WPS Office and open an existing workbook or create a new spreadsheet.
- 2. Create the source entries: Enter consistently formatted items such as "John Smith - 33346" in a dedicated source range.
- 3. Configure Data Validation: Select the destination cell, choose Data > Data Validation, select List, and assign the source range.
- 4. Add the extraction formula: In the result cell, enter =IFERROR(--TEXTAFTER(A2," - "),"") to return the number from the selected entry.
Frequently Asked Questions
How do I create a drop-down list in WPS Spreadsheet?
Select the destination cell, go to Data > Data Validation, choose List, specify the source range containing the entries, and confirm the dialog.
What formula returns the number after the hyphen?
If the selected entry is in A2 and uses the separator " - ", enter =IFERROR(--TEXTAFTER(A2," - "),"") in the result cell.
Why does the formula return a blank cell?
IFERROR returns a blank when A2 is empty or does not contain the expected separator. Check that the selected entry includes " - " and that the formula references the correct cell.
Can I search within a WPS Spreadsheet drop-down list?
Search or type-ahead behavior varies by version. For large lists that need dedicated search controls, use a Combo Box from the Developer tools and link it to a worksheet cell.
Can WPS Spreadsheet open Excel drop-down lists and formulas?
WPS Spreadsheet supports Microsoft Excel XLSX files and commonly used Data Validation lists and formulas, although advanced controls or version-specific search behavior may differ.
