Excel Hacks Ebook [Electronic resources] نسخه متنی

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$1)

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.