Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

migrated from Jan 7 '13 at 22:28

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

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
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!


would replace any linebreak with the word symbol for the line break. And the nested formula
");" ";"_")
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

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 :

share|improve this answer

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.

share|improve this answer
This method will replace spaces, but not all non-printing characters, e.g. CHAR(10) (a carriage return within a cell, also achieved using Alt-Enter) – Freda K Jan 23 at 0:45

Changing the font to the type "Terminal" would help you see and alter them.

share|improve this answer

Your Answer


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.