Excel counta is counting blank cells as if they had data. A lengthy fix is given

[PondyDave]

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.

[#2]

Can you please include some sample data from your file? Preferably anonymized.

[#3]

Hello @PondyDave,

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!
PReagan

[#4]

ahahahaha nooooo

The cells did have a formula in them that returned "", i.e. they had an iferror on an index/match, and returned nothing. And excel counts them as containing text. But the formula is gone, with copy/paste values. So the cell contains nothing to you and me, but it sorts and counts as if it contained data. 

It just happened to me. Thanks to the OP, fixed now.

Super irritating excel moment, 3 stars.

@PReagan 

[#5]

@jude234 

You all could have used a bit of VBA code and sit back and relax:

Sub ClearCellsEmptyText()
    Dim cl As Range
    Dim v As Variant
    For Each cl In ActiveSheet.UsedRange
        If not cl.HasFormula Then
            v = cl.Value
            'Remove Enter, line break, carriage return, non-breaking space, spaces
            v = Replace(v, Chr(160), "")
            v = Replace(v, Chr(10), "")
            v = Replace(v, Chr(12), "")
            v = Replace(v, Chr(13), "")
            v = Trim(v)
            'If nothing remains, clear the cell
            If Len(v) = 0 Then
                cl.ClearContents
            End If
        End If
    Next
End Sub

[#6]

@PondyDave A simplified solution is to use Find & Select > Replace. Leave the "Find what:" box blank, then "Replace with:" a text string or symbol that doesn't currently exist in the selected range or worksheet. For example, use the Greek Small Letter Lambda symbol "λ", then click "Replace All":

Find and Replace: Step 1

Then repeat, but with "λ" in the "Find what:" box, and the "Replace with:" box blank:

Find and Replace: Step 2

All cells that previously contained an empty string will now be blank. Cheers!