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)?
67 Answers
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 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 :
1CTRL+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.
1Changing the font to the type "Terminal" would help you see and alter them.
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).
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.
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
