Hack 26 Use Replace... to Remove Unwanted Characters

   

Hack 26 Use Replace... to Remove Unwanted Characters

When importing data or copying and pasting data from other sources into Excel, unwanted characters appear throughout your spreadsheet. Using this hack, you can spare yourself the trouble of removing them by hand .

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:

 =CODE($A) 

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 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.