How to display or view non-printing characters in Excel?

Question

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

Answer 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

Answer 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.

Answer 4

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

Answer 5

Doesn't exactly answer your question, but I set number format to this:

;;;'@'

for single quotes, or this

;;;\"@\"

for double quotes. That wraps quotes around any text entered. I also set font to Courier New (or any other fixed-width font).

Answer 6

1 Use find and enter space

2 Do Replace All and type in "[s-p-a-c-e]"

3 Optional: If you also want to highlight the whole cell in red too, just use the format selector next to that

Result: Those pesky spaces will reveal themselves super clearly

Why did I need to do this: I used the COUNTA function to find nonblank cells in a column. However it was returning a number larger than I expected. I debugged each cell one by one, and to my amazement, some apparently blank cells showed COUNTA=0 and others showed COUNTA=1 which makes no sense. I could NOT see the different between the two. It turns out a single leftover blank counts in that function, but it's not visible ANYWHERE in either the cell or the entry box at the top.

Conclusion: If you are relying on COUNTA for important tasks, you had better be sure it's not counting those troublesome spaces that you may not know are there.

Answer 7

I don't usually need VBA, so I prefer doing excel stuff in python + openpyxl

from docx import Document #word docx py library
import openpyxl #excel py library
from openpyxl.styles import Color, PatternFill, Font, Border
from openpyxl.styles import colors
from openpyxl.cell import Cell
import re #regular expressions
import os #work with system

wb = openpyxl.load_workbook('test.xlsx') #open needed document

redFill = PatternFill(start_color='FFFF0000',
                   end_color='FFFF0000',
                   fill_type='solid')        #function to fill bad cells red

n = 0
print (wb.sheetnames)       #print all sheetnames to ensure theres no hidden

for sheet in wb.worksheets:  #cycle through sheets in excel file
    # get max row count
    max_row=sheet.max_row
    # get max column count
    max_column=sheet.max_column
    for i in range(1,max_row+1):
     # iterate over all columns
        for j in range(1,max_column+1):  #cycle through all rows and columns
            # get particular cell value    
            cell_obj=sheet.cell(row=i,column=j)
            s = re.search('[^-*+()!№;%:?@#$%^&;:_=/\\a-zA-Z0-9\ а-яА-Я°\'\".,,.«»<>ёЁ]', str(cell_obj.value)) #find bad symbols with regular expression
            #^ find not normal characters 
            #s = re.search('[\n]', str(cell_obj.value)) find line end
            if s:   
                print(n, " ", i, " ", j) #sheet, row, col
                #print("^", s, "^") print bad symbol
                #sheet.cell(row=i,column=j).fill = redFill
                #color current cell wth spec chars red

    print(n)
    n+=1

wb.save("test.xlsx") #save redacted book