How to Remove Duplicates Ignoring Spaces in Microsoft Excel and Google Sheets

Microsoft Excel

1. Start by opening your Excel workbook which contains the data from which you want to remove duplicates.
2. Suppose your data is in column A starting from cell A1. In cell B1, adjacent to A1, insert the formula =SUBSTITUTE(A1," ","") and press Enter. This formula uses Excel's SUBSTITUTE function to remove all spaces in the cell value.
3. Drag the fill handle (small square at the bottom-right corner of the cell) down to apply this formula to all cells in column B.
4. Now, select the range in column B, navigate to the Data tab in the Ribbon, and in the Data Tools group, click on Remove Duplicates, a feature Microsoft provides to easily identify and eliminate duplicate values.
5. Make sure to uncheck My data has headers box if your data doesn't have a header, and then click OK. This will remove all duplicate entries from your selected data.

Google Sheets

1. In your Google Sheets document, if your data is in column A starting from cell A1, in the adjacent cell B1, input the formula =SUBSTITUTE(A1," ","") and press Enter. Google Sheets' SUBSTITUTE function will remove all the spaces in the cell value, just like in Excel.
2. Extend this formula to all cells in column B by dragging the fill handle down.
3. Now, select the range in column B, click on the Data menu, and select Data validation.
4. In the Data validation dialog box, under the Criteria section, select Custom formula is, and input =COUNTIF($B$1:$B1, B1)=1 (assuming your data starts from cell B1). This data validation feature ensures only unique entries are considered.
5. Finally, click Save to apply these settings.

Summary This tutorial shows you how to remove duplicates ignoring spaces in Microsoft Excel and Google Sheets. It demonstrates the use of functions like SUBSTITUTE and features like 'Remove Duplicates' or 'Data Validation' to cleanse your data of unwanted duplicate entries.