Excel not recognizing spaces

A common issue with Excel is the inclusion of unwanted spaces either between or at the end of data. This often occurs when receiving information from another system via a download. Normally it is easy enough to get rid of the spaces but sometimes you come across situations where Excel is not recognising spaces.

A typical situation is that you delete the ‘space’ in a single cell and everything works. But when you try and replace/ change many cells (using FIND/ REPLACE tool or another method) Excel says it can’t find any spaces.

The most common reason for this is that the ‘space’ in the cell is not a space caused by hitting the space bar but a different ‘space’. There are lots of ways of understanding what this ‘space’ is but to just sort out the issue,

  • choose one of the cells and highlight and copy the ‘space’.
  • Then paste this ‘space’ into your FIND/ REPLACE tool or the formula you are using.

This should work. When you run it, you should see the changes you expect.

To learn more about all the cleaning tips in Excel, have a look at our online course