Not all characters can be visibly displayed in a
worksheet cell. For example, embedded control characters like a tab may not
appear in a cell. This happens quite frequently when you copy and
paste text from the web or a Word document into Excel. The CellView add-in
allows you to see the exact characters in a cell, as well as their character
codes (in either decimal or hex numbers).
Download and unzip this file. Then, go to the Tools menu, choose Add Ins, click Browse and navigate to the folder in which you unzipped the file. Choose the XLA file. When you install this add in, it will add an item to your View menu called "View Cell Contents". Click this item to display the view screen, shown below.
The first line of the display shows the character position in the cell. The second line shows the actual character if it is a standard printable character or a blank if it is a non-printable character. The third line shows the character code for the character, in either decimal or hex (check the hex box to display hex numbers). You can change the starting position of the display by changing the value in the "Start At Char" drop down.
Special characters are indicated by a red ^ character in the fourth line of the display.
You are free to examine and modify the code if you wish. The VBA Project password is 'a'.