How to display or view non-printing characters in Excel?
Is there an option in MS Excel 2010 that will display non-printing characters within a cell (e.g. spaces or the linebreak character introduced by pressing Alt-Enter)?
Solution 1:
While you cannot show special characters directly in the cell, you could use a formula in the adjacent (inserted) column to replace Enters and Spaces with characters of your choice!
E.g.
=SUBSTITUTE(A1;" ";" ¶ ")would replace any linebreak with the word symbol for the line break. And the nested formula
=SUBSTITUTE(SUBSTITUTE(A1;" ";" ¶ ");" ";"_")will replace both, space and enter. (Note: in order to enter an "Enter" in the formula, you need to press
Alt+Enter while editing the formula.
Solution 2:
The easiest way to do it is to simply change the font to a font that has a build-in visible glyph for the space (or any other character that you may need to identify)
Unfortunately, I don't have any good example of such a font to provide you, but it's very easy to add a small dot to an existing font, using any font editor software. Just don't forget to rename the font (not the font file, but the font NAME inside the font file), so that it's easy to distinguish this custom font from the original one if you have both installed.
EDIT I've finally found the time to make such a font ! Here comes DottedSpace Mono, based on Bitstream Vera Sans Mono, but with build-in dotted spaces :
http://github.com/tanusoft/DottedSpaceMono
Solution 3:
CTRL+H replace all the spaces with a ~ This will help quickly for spaces without programming , and to reverse just replace ~ with " ".
Best program I found for comparing these types of files where text is not displaying is Ultra Edit. Had to use it to compare EDI Files, interface files , technical uploads etc. MS Office just is not well equipped for the task.
Solution 4:
Changing the font to the type "Terminal" would help you see and alter them.