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)?
migrated from stackoverflow.com Jan 7 '13 at 22:28
This question came from our site for professional and enthusiast programmers.
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!
=SUBSTITUTE(A1;" ";" ¶ ")would replace any linebreak with the word symbol for the line break. And the nested formula
=SUBSTITUTE(SUBSTITUTE(A1;" ";" ¶ ");" ";"_")will replace bot, space and enter. (Note: in order to enter an "Enter" in the formula, you need to press
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 :