5

In LibreOffice Calc the =BLANK() formula will only return TRUE on cells with the underlying datatype ”blank”. In other words: If the cell contains a formula that returns nothing (or if it contains the empty text string), =BLANK() will be FALSE. Is there a way to check if a cell contains displayable content, such as a text string or a number?

In the screenshot below, the =ISBLANK(B3) formula in C3 returns FALSE, as the cell contains a formula returning nothing. How would I go about to check is a B:B cell is ”blank” in the sense of not containing any data?

Image showing a ”blank” field not trigegring ISBLANK

leo
  • 152
  • 7
  • 2
    Hmm - with `=""` in F2 (providing an empty string as lookup value for `b`) and `=LEN(B3)=0` in C3, you could check if cell B2 has an empty string as content. But i fear this is just a dirty workaround... – tohuwawohu Oct 12 '21 at 14:23
  • 1
    @tohuwawohu That's a clever hack! (Though one I'd prefer not to have to maintain over time...) – leo Oct 12 '21 at 14:50
  • @tohuwawohu Until the `ISBLANK` is extended to include empty strings, I wouldn't call that a workaround but a solution. Combine the tests with logical "or" and that's it. – Vilinkameni Oct 13 '21 at 07:24

2 Answers2

5

There are different ways to work around the limitations of ISBLANK():

  • As described in my comment, you may provide explicitly an empty string as lookup value in Column F for "b" (with ="" in F2), so you can test the LEN() of B's content.

  • Another option would be to "extract" the value of the B column's cells using the CELL() function together with the "Contents" InfoType, and check the result. If cell F2 (lookup value for "b") is empty, =CELL("CONTENTS"; B3) returns "0". The following formula returns TRUE for B3 and B4. If all your lookup values are either empty or !=0, you could use the following function in col C to check for empty results without modifying your lookup table:

    =(CELL("CONTENTS";B1)= 0)

While the first solution requires editing your lookup table, the second works "out of the box", but it assumes that there's no "0" lookup value.

tohuwawohu
  • 1,132
  • 2
  • 10
  • 19
  • `=""`, `=LEN(B3)=0` and `=CELL("CONTENTS";B3)` almost work, but if the source cell contains a space character this is 'visibly' blank, but yet it returns false unexpectedly. – Elliptical view Sep 13 '22 at 19:34
1

If the cell is empty, or has one or more blanks (so is not 'visible'), then you might want to use this instead to test it instead of simply =LEN(B3)=0:

=len(trim(b3))=0
Elliptical view
  • 3,559
  • 4
  • 25
  • 44