If you have used Excel for your every day job, I am sure you will have come across this issue:

• You've copied data from somewhere, and you think it's a number, but Excel doesn't.
• You try replacing spaces with nothing, but it stubbornly refuses to change.
• You try the CLEAN function... you add 0.. nothing works.
• Fed up, you remove the spaces by hand but it takes you all day

Now, the trick here is to understand that what looks like a space actually isn't. It's a completely different character that is displayed as a space.

Here's a screen shot:

The trick here is to:

1. Go into the cell
2. Copy the character that looks like a space (but isn't)
3. Press CTRL + H (to replace)...
4. Paste that character into the replace window
5. Replace it with nothing.

Download sheet to practise how to Remove unwanted characters in Excel

Training Video on how to Remove unwanted characters: