07-24-2019 03:07 AM
07-24-2019 03:07 AM
My mega spreadsheet is full of invisible junk scattered across cells. It is 1300 rows deep by 500 columns wide. Each bit of junk appears in the totals when I counta the rows or columns. I discovered it by accident when looking at a row with a counta of 3 yet only two filled cells. The junk needs to be found and cleared...the cells must not be deleted.
Nothing on any help page on excel or the internet told me how to deal with the problem. People have the problem but there is no automatic way to deal with it and no help is described as far as I can see.
To find empty cells Excel has decided there is something in is Find & Select then Constants. This paints every occupied cell blue. Do not touch any cell with the cursor or you have to do that bit again.
Then in rows of 25, jump a page view a time using the linear page mover (not a key ) look though each visible page and note the coordinations of the blank but blue cells. Also record each set of 25 rows you have looked at.
Jump down to move down a page then do the next 25 rows.
You cannot delete from the blue celled screen.
When back in normal spreadsheets find each cell and right click clear contents.
Only way I can find to clear invisible stuff.
Copying the sheet to a new spreadsheet using only text does not work.
Filling all the blanks using replace all blanks and e.g. x123 and then deleting all x123 filled cells does not work.
It is wise to counta all your rows first and have that column on the left all the time so you can see the count fall as you clear the empty cell. Save every action.
07-24-2019 07:11 AM
07-24-2019 08:11 AM
Are these cells with invisible junk formatted so that the text is white (or matches the cell background color?)
If so, you can press ctrl+F (brings up the Find and Replace menu) > Select Replace > In "Find what:" leave contents blank but click Format and change the Font color to white (or whatever color your invisible cells font is) > Click OK > In "Replace with:" leave the contents blank > Click "Replace All".
Hope this helps!