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)?

Answer

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.