67. Excel Tips - Remove unwanted characters

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:
- Go into the cell
- Copy the character that looks like a space (but isn't)
- Press CTRL + H (to replace)...
- Paste that character into the replace window
- Replace it with nothing.
Download sheet to practise how to Remove unwanted characters in Excel
Training Video on how to Remove unwanted characters:
Attachment | Size |
---|---|
Remove-unwanted-characters.xls | 17 KB |
»
- Nick's blog
- Login or register to post comments
- 14205 reads
Recent comments
5 years 50 weeks ago
6 years 36 weeks ago
6 years 47 weeks ago
6 years 50 weeks ago
6 years 51 weeks ago
7 years 5 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago
7 years 13 weeks ago