Have you faced the problem where you have cut/paste data from the web and get invisible whitespace character(s) that prevents you from using the value as a number in calculations such as the SUM function. You may find the TRIM and SUBSTITUTE functions will not locate it with the normal white space, blank, or space notations.
The offending character is often the ASCII character 0160, which can be represented with the Char(160) function. This is a character you can not enter with the keyboard or an escape sequence.
Solution:
Find an empty cell, select it and look at the formula bar (it’s empty).
Next, type the following without hitting enter, =CHAR(160), STOP, don’t hit enter!
Glance at formula bar, hit F9 while the focus is on that cell your in, see the formula bar has changed to the special character. Select it, and use Ctrl-C to copy it into your memory cache. Now you have the character available to paste into other areas.
Select group of Excel cells you want to clean.
Use Ctrl-H to open the Find and Replace pop-up window, with Replace selected as default tab. With Ctrl-V place the unseen whitespace character in the “Find what” field and then ensure the “Replace with” is blank. With “Replace all” command button you will clean out all those characters.
Did that not work for you? What character is your mystery character? To the right of the cell, add the following and adjust the cell reference; =UNICODE(MID($J20,1, 1)). This will give you the code.
If you don’t want to replace the characters, you can use the MID command in another cell to remove the 1 character you don’t want. Try this command once you get the code; =SUBSTITUTE(A1, CHAR(160),””). Substitute 160 for the code you find.
The Life Blog 