1

Apparently in earlier versions of Excel, there was an option "Show Control Characters" which seems to be missing from 2013. Is there a way to display hidden characters in a worksheet?

I have a spreadsheet where one of the columns has a lot of seemingly blank rows but which cannot be selected using Find & Select > Go To Special > Blanks which makes me suspect that there is at least one hidden character in these cells which is not being shown.

The ultimate goal is to make these cells truly blank so they can be selected using the select-blanks command and manipulated all at once.

Edit:

I have already looked at this thread but none of the answers were helpful in this situation.

Community
  • 1
Jason L.
  • 111
  • 1
  • 1
  • 8
  • 3
    [I'd start here](http://www.mrexcel.com/forum/excel-questions/93354-display-control-characters.html) or even try [this](http://superuser.com/questions/529585/excel-2010-how-to-display-or-view-non-printing-characters) – Raystafarian Nov 23 '15 at 17:58
  • Installing the add-on is not an option right now and the macro returns "No special characters found". – Jason L. Nov 23 '15 at 18:01
  • 3
    I'm pretty sure "Show Control Characters" didn't exist in previous versions of Excel - AFAIK that's a Word option (only). – Ƭᴇcʜιᴇ007 Nov 23 '15 at 18:15
  • Raystafarian, I'd also already looked at that other thread. Tried the substitute and clean functions in an adjacent column with no luck. Also tried installing DottedSpace Mono, it showed nothing in the blank-but-not-blank cells. – Jason L. Nov 23 '15 at 18:19
  • Ƭᴇcʜιᴇ007, I saw it [here](http://answers.microsoft.com/en-us/office/forum/office_2007-excel/how-to-find-hidden-characters-in-excel-spreadsheet/32c66645-b66c-48c7-b2d8-3c09a050c1d1?db=5) referring to Excel 2007, but who knows if that's actually accurate. – Jason L. Nov 23 '15 at 18:21
  • Can you use Excel's filter function to find these rows? – Kyle Nov 23 '15 at 19:21
  • Kyle, wow, I would not have thought this would work but for some reason it does. Even though select-blank does not recognize them as blank cells, the Filter function does. If you filter by blank cells, you can select them and delete the cells, then when you remove the filter, you can do select-blank and it functions as expected. Technically this doesn't answer the question but it gets me the results I wanted. Thanks a lot!! – Jason L. Nov 23 '15 at 19:50
  • Frequently those cells contain `NBSP` -- `CHAR(160)` which you could search for. But I see you have a different solution. – Ron Rosenfeld Nov 25 '15 at 01:38
  • @Kyle - can you add an answer so that it can be voted on? – AdamV Dec 03 '15 at 11:42

2 Answers2

0

It appears, from my own research, the comments on the question, and lack of answers, that it is not possible to natively show hidden characters in Excel 2013.

As Raystafarian's comment pointed out, there is an add-on which claims to do this but I was unable to test it.

Jason L.
  • 111
  • 1
  • 1
  • 8
0

Excel's AutoFilter feature distinguishes between blank cells and cells that appear blank, but in fact have one or more non-printable characters.

To see for yourself, enter this formula and filter: =CHAR(1).

Kyle
  • 2,406
  • 2
  • 11
  • 12