Forum Discussion

PondyDave's avatar
PondyDave
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's avatar
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's avatar
    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's avatar
      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

Resources