How to Highlight Duplicate City Names with Partial Matches in WPS Office Spreadsheets
Question details
The user needs to identify duplicate city names within each country when the city column contains extra descriptions in parentheses.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains city names such as Paris (Downtown) and Paris (North) in one column, with a separate country column or country code. The user wants duplicate city names flagged even though the entries are not exact matches.
- Observed behavior
- Simple duplicate matching does not flag entries like Paris (Downtown) and Paris (North) as the same city because the parenthetical descriptions make the cell values different.
Open your workbook in WPS Office Spreadsheets and confirm the column that contains city names. If you need to compare duplicates within each country, make sure the country or country code is in its own column.
Highlight partial duplicate city names with a conditional formatting formula
Use this method to highlight duplicates directly in the existing list without adding extra columns. It normalizes each city name before the opening parenthesis and optionally compares the country column.
The formula strips the parenthetical description with LEFT and FIND, so Paris (Downtown) and Paris (North) both normalize to Paris.
This approach works in WPS Office Spreadsheets and does not require the Microsoft 365 TEXTBEFORE function.
Select the cells you want to format, for example C3:C3002. If you also compare by country, make sure the country column, such as B3:B3002, is present.
In WPS Office Spreadsheets, go to the Home tab, click Conditional Formatting, and choose New Rule.
Select Use a formula to determine which cells to format and enter the city-only formula: =SUMPRODUCT(--(TRIM(LEFT($C$3:$C$3002,FIND("(",$C$3:$C$3002&"(")-1))=TRIM(LEFT($C3,FIND("(",$C3&"(")-1))))>1. To compare within each country, add the country condition: =SUMPRODUCT((TRIM(LEFT($C$3:$C$3002,FIND("(",$C$3:$C$3002&"(")-1))=TRIM(LEFT($C3,FIND("(",$C3&"(")-1)))*($B$3:$B$3002=$B3))>1.
Click Format, choose a fill color or text color, and click OK until all dialog boxes close. Cells whose normalized city name appears more than once will now be highlighted.
Use a helper column and COUNTIFS for an easier audit trail
Use this method when you prefer to see the normalized city value in a separate column and then apply a standard COUNTIFS conditional formatting rule.
Highlight Duplicate City Names with Partial Matches in WPS Office Spreadsheets
Open your workbook in WPS Office Spreadsheets, apply a conditional formatting formula that strips parenthetical descriptions, and optionally compare the country column. WPS Office gives you a free, familiar spreadsheet tool with Excel-compatible conditional formatting.
- 1. Select the range to evaluate: Open your workbook in WPS Office Spreadsheets and select the city cells you want to check. If you compare by country, keep the country column available.
- 2. Create a formula-based rule: On the Home tab, click Conditional Formatting > New Rule, then select Use a formula to determine which cells to format.
- 3. Enter the duplicate-matching formula: Enter =SUMPRODUCT((TRIM(LEFT($C$3:$C$3002,FIND("(",$C$3:$C$3002&"(")-1))=TRIM(LEFT($C3,FIND("(",$C3&"(")-1)))*($B$3:$B$3002=$B3))>1 and adjust the columns to your workbook.
- 4. Format and confirm: Click Format, choose a highlight style, and click OK. Duplicate city names with the same normalized city and country will be highlighted.
Frequently Asked Questions
Does this work if my city entries do not all contain parentheses?
Yes. The formula appends an opening parenthesis before FIND, so a cell like Paris and a cell like Paris (Downtown) both produce Paris as the normalized city name.
How do I highlight duplicates within each country instead of the entire list?
Add the country condition to the rule. With countries in column B and city names in column C, use =SUMPRODUCT((TRIM(LEFT($C$3:$C$3002,FIND("(",$C$3:$C$3002&"(")-1))=TRIM(LEFT($C3,FIND("(",$C3&"(")-1)))*($B$3:$B$3002=$B3))>1 and adjust the ranges.
Can I use TEXTBEFORE instead of LEFT and FIND?
If your version of WPS Office Spreadsheets or Microsoft 365 supports TEXTBEFORE, you can use it to extract the text before the parenthesis. The LEFT/FIND method is used here because it is compatible with more versions.
Can I highlight entire rows instead of only the city cells?
Yes. Select the full table range before creating the rule, keep column references such as $C3 and $B3 row-relative, and choose a format that applies to the entire selected row.
