Forum Discussion

Copper Contributor

Jul 24, 2019

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

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

excel

PReagan

Bronze Contributor

Jul 24, 2019

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

jude234

Copper Contributor

Nov 30, 2023

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

JKPieterse

Silver Contributor

Nov 30, 2023

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