Excel's Replace... feature can help you remove unwanted characters from your spreadsheet, but it takes a few extra steps. For instance, you can replace cells containing unwanted characters with nothing (effectively deleting them) so that they no longer exist. To do this, you need to know the character code of the characters you want removed. All characters have a character code, and Excel will tell you what it is if you apply the
CODE function to them. The
CODE function returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer.
To make this work, select one of the cells containing an unwanted character. From the Formula bar, highlight the character and copy it to the clipboard. Then select any unused cell (A1, for example) and paste the character into the cell on its own.
In another cell, enter the following formula:
This returns the character code of the unwanted character.
Select all your data and select Edit » Replace.... Click the Find What: field, press the Alt or key, and enter
0 followed by the code number the
CODE function returned. If the code number is 163, press the Alt or key and type
0163. Leave the Replace With: field empty and click Replace All. This will very quickly remove all the unwanted characters matching that character code. Repeat these steps for each unwanted character.