Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
you can put =IF(CLEAN(A1)=A1,"NA","Needs Cleaning") in the cell next to the cell next to it, or you can use conditional formatting using the char() (or chr in VBA) notation to search for carraige returns (char(13)) or any other character, here is a link to the char numbers –  scott Jan 7 '13 at 19:40
    
What is your reason for needing to see them? Unless you just like to see them, there are ways we might be able to help you achieve another objective (e.g., remove all line breaks from a column of cells) –  EntropyWins Jan 7 '13 at 23:46
    
The goal of displaying the non-printing characters is simply to proof-read the content of the cells. Since the content is technical and carriage returns are common (and expected) within it, this is something that needs to be done by hand. –  Freda K Jan 8 '13 at 0:51
    
@scott Thanks very much for the note about using CLEAN(). That function will be very useful, although it's sadly not going to help for this specific need. –  Freda K Jan 8 '13 at 1:11
    
It sounds like you're using the wrong program for your task, but I understand that can be unavoidable at times. Why can't you use a table in Microsoft Word? That said, I put an answer to this specific question below. –  EntropyWins Jan 8 '13 at 3:24
add comment

migrated from stackoverflow.com Jan 7 '13 at 22:28

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 1 down vote accepted

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 bot, space and enter. (Note: in order to enter an "Enter" in the formula, you need to press Alt+Enter while editing the formula.

share|improve this answer
    
Love the work around, why didn't I think of that! –  EntropyWins Jan 8 '13 at 21:35
    
Thank-you very much! I'll do that. –  Freda K Jan 8 '13 at 21:40
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.