Function Problems

Identify Duplicate Values with Extra Spaces in Excel

Steven ParkerSteven Parker Nov 18, 2026 868 views

Question details

Identify values that are duplicates when differences in ordinary spaces are ignored.

How to Identify Duplicate Values with Extra Spaces in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Normalize the values

If your values are in C2:C100, enter =SUBSTITUTE(C2," ","") in D2 and fill the formula down through D100.

2
Flag duplicate values

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.

3
Review the matches

Look for TRUE in column E, or select the table and use Data > Filter to show only TRUE values.

Find duplicates in a list with a helper column
What the comparison ignores: This method ignores all ordinary spaces and is not case-sensitive. It leaves blank normalized values unflagged and does not remove nonbreaking spaces or other hidden characters.
Find duplicates in WPS Spreadsheets

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. 1. Open your spreadsheet: Open WPS Spreadsheets and select Open to load the workbook containing your list.
  2. 2. Remove spaces in a helper column: For values in C2:C100, enter =SUBSTITUTE(C2," ","") in D2 and fill it down through D100.
  3. 3. Flag matches: In E2, enter =IF(D2="","",SUMPRODUCT(--($D$2:$D$100=D2))>1) and fill the formula down through E100.
  4. 4. Review the results: Check column E for TRUE values or use Data > Filter to display the flagged rows.
Flag duplicate text despite different ordinary spacingKeep original values visible alongside comparison resultsOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and filters
microsoft office alternative - wps office

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.