Fix Duplicate Month Names in a WPS Excel Drop-Down List
Question details
The user wants to remove duplicate month names from an Excel drop-down list after noticing that two displayed months appear identical but are treated as separate entries.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a data validation drop-down list of months in an Excel workbook and needs the list to show each month only once.
- Observed behavior
- Duplicate month names remain in the list because the source cells contain trailing spaces, nonbreaking spaces, hidden characters, inconsistent formatting, or mixed text and date values.
Select the source range that feeds the drop-down list and check for extra spaces, nonbreaking spaces, hidden characters, and mixed text/date formatting before rebuilding the validation list.
Clean the Source Month Values with a Helper Formula
Use this recommended method when duplicate month names are caused by trailing spaces, nonbreaking spaces, or other invisible characters in the source cells.
The formula removes common space characters, nonprinting characters, and nonbreaking spaces from the month source data. After cleaning, create the drop-down from the cleaned unique range so WPS Spreadsheets treats matching month names as the same value.
Click the header of the column next to your month source list, right-click, and choose Insert to add a blank helper column.
In the first helper cell next to the first month value, type =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) and press Enter. Replace A2 with the first cell of your source month list.
Drag the fill handle from the first helper cell down through all source month rows to clean the entire list.
Select the cleaned helper values, click Data > Remove Duplicates, confirm the column, and click OK so only unique month names remain.
Select the cell where the drop-down is needed, click Data > Validation, choose List, and set the Source to the cleaned unique range.

Use Remove Duplicates to Clean the Existing Source List
Use this direct method when the source month list is already close to clean and you only need to remove duplicate rows before creating the validation list.
Check for Mixed Text and Date Month Values
Use this method when a month such as May appears twice because one cell is stored as text and another is stored as a date, even after cleaning spaces.
Create a Clean Month Drop-Down List in WPS Spreadsheets
WPS Office Spreadsheets lets you clean month source data, remove duplicates, and create a drop-down list with unique values using the Data Validation feature.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the Excel workbook that contains the month source list and the drop-down cell.
- 2. Clean the month source values: Use a helper column with =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) to remove trailing spaces and hidden characters.
- 3. Remove duplicate month names: Select the cleaned month range, click Data > Remove Duplicates, confirm the column, and click OK.
- 4. Create the drop-down validation: Select the target cell, click Data > Validation, choose List, and set Source to the cleaned unique month range.
- 5. Test the drop-down list: Click the validation cell and open the drop-down arrow to confirm each month appears only once.

Frequently Asked Questions
Why does my Excel drop-down list show the same month twice?
Duplicate-looking months are often caused by trailing spaces, nonbreaking spaces, hidden characters, or a mix of text and date values in the source range.
How do I clean nonbreaking spaces from month names in WPS Spreadsheets?
Use the formula =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) in a helper column and fill it down to remove regular spaces, nonprinting characters, and nonbreaking spaces.
Can I make the drop-down list use only unique month names?
Yes. Create a unique source range by selecting the cleaned values and clicking Data > Remove Duplicates, or use the UNIQUE function if your WPS version supports it, then set the validation Source to that range.
Where is Data Validation in WPS Spreadsheets?
Select the cell where you want the drop-down, click the Data tab on the ribbon, and then click Validation. In the dialog, choose List and set the Source range.
What if May appears twice because one value is a date and the other is text?
Select all source month cells, open Format Cells from the Home tab, and apply the same format to all cells. Then create the drop-down from the corrected list.




