14

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

6
  • 2
    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)
    – PatKilg
    Jan 7 '13 at 23:46
  • 1
    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.
    – PatKilg
    Jan 8 '13 at 3:24
2

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.

1
  • Love the work around, why didn't I think of that!
    – PatKilg
    Jan 8 '13 at 21:35
10

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

1
  • 1
    I do not see linefeeds, for example.
    – zylstra
    Oct 4 '16 at 1:56
4

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.

1
  • 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 '16 at 0:45
2

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

0

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

0

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.

0

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

1
  • Generally, answers are much more helpful if they include an explanation of what the code is intended to do, and why that solves the problem without introducing others.
    – MMM
    Dec 6 '19 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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