It is common to extract data from a database into an excel spreadsheet. It is also common for the results to have funky characters, lead and trailing spaces and other such nonsense that mess up the formatting. We can pull out our hair trying to guess what these characters are and it is not fun dealing with them so our formulas work.
Here is a step-by-step process to identify these characters and remove them.
- Copy the cell containing the mystery character.
- Switch to Word and choose paste special/non-formatted text – this will paste the funky characters as well as the cell content.
- Display paragraph marks in Word.
- Place the cursor just to the right of the mystery character and press ALT-X.
- The mystery character will change to the Unicode representation.
- The Unicode character can be found in this table and the resultant Ascii character code can be referenced by clicking on the Unicode for your mystery character.
- With the Ascii code # in hand, you can now do a global find/replace command.
- Select the column, row or whole spreadsheet according to where you want to find and replace.
- In the Find box, hold down the ALT key and type the four character code – notice that this has to be a four character code, so if your Unicode was 00A0 with resultant Ascii of 160, you hold down ALT and type 0160. So add leading zeros to get to four characters. Also, for entering codes on the mac, see this page.
- Make sure the Replace with: box is blank, which will just remove the mystery characters with nothing.