Identify Duplicate Values with Extra Spaces in Excel
Question details
Identify values that are duplicates when differences in ordinary spaces are ignored.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to compare text values or find duplicates in a list where spacing varies.
- Observed behavior
- Values that look identical may not match because they contain different numbers of spaces.
Identify the cells or column you want to compare and choose an empty column for any helper formulas. The formulas below remove all ordinary spaces for comparison, including spaces between words.
Find duplicates in a list with a helper column
Use this method to flag every matching value in a range while keeping the original text unchanged.
If your values are in C2:C100, enter =SUBSTITUTE(C2," ","") in D2 and fill the formula down through D100.
Enter =IF(D2="","",SUMPRODUCT(--($D$2:$D$100=D2))>1) in E2, then fill it down through E100. Adjust the range to match your list.
Look for TRUE in column E, or select the table and use Data > Filter to show only TRUE values.

Compare two Excel cells without ordinary spaces
Use a single formula when you only need to check whether two values match.
Compare Text Without Extra Spaces in WPS Spreadsheets
Use SUBSTITUTE and a helper column in WPS Spreadsheets to flag values that match when ordinary spaces are ignored.
- 1. Open your spreadsheet: Open WPS Spreadsheets and select Open to load the workbook containing your list.
- 2. Remove spaces in a helper column: For values in C2:C100, enter =SUBSTITUTE(C2," ","") in D2 and fill it down through D100.
- 3. Flag matches: In E2, enter =IF(D2="","",SUMPRODUCT(--($D$2:$D$100=D2))>1) and fill the formula down through E100.
- 4. Review the results: Check column E for TRUE values or use Data > Filter to display the flagged rows.

Frequently Asked Questions
Why doesn't Excel recognize two values as duplicates?
The cells may contain different numbers of spaces or other hidden characters. Compare them with =SUBSTITUTE(C4," ","")=SUBSTITUTE(C5," ","") to ignore ordinary spaces.
Does SUBSTITUTE remove spaces between words too?
Yes. =SUBSTITUTE(C2," ","") removes every ordinary space from the formula result, including spaces between words. It does not change the original cell.
Can I remove the duplicates after identifying them?
Yes. After reviewing the flagged rows, select the relevant data and use Data > Remove Duplicates. Choose the normalized helper column as the comparison field and check which rows will be removed.
What if the values still don't match after removing spaces?
They may contain nonbreaking spaces, tabs, or other differences. To remove nonbreaking spaces as well, use =SUBSTITUTE(SUBSTITUTE(C2,CHAR(160),"")," ","") in a helper cell.




