/text/scalc/05/empty_cells.xhp empty cells;handling of i86303 Handling of Empty Cells In older versions of the software, empty cells were forced to numeric 0 in some contexts and to empty string in others, except in direct comparison where =A1=0 and =A1="" both resulted in TRUE if A1 was empty. Emptiness now is inherited until used, so both =VLOOKUP(...)=0 and =VLOOKUP(...)="" give TRUE if the lookup resulted in an empty cell being returned. A simple reference to an empty cell is still displayed as numeric 0 but is not necessarily of type numeric anymore, so also comparisons with the referencing cell work as expected. For the following examples, A1 contains a number, B1 is empty, C1 contains the reference to B1: Case Formula Results and comments A1: 1
B1: <Empty> C1: =B1 Displays 0 =B1=0 TRUE =B1="" =C1=0 =C1="" TRUE (previously was FALSE) =ISNUMBER(B1) FALSE =ISNUMBER(C1) FALSE (previously was TRUE) =ISNUMBER(VLOOKUP(1;A1:C1;2)) FALSE (B1) =ISNUMBER(VLOOKUP(1;A1:C1;3)) FALSE (C1, previously was TRUE) =ISTEXT(B1) =ISTEXT(C1) =ISTEXT(VLOOKUP(1;A1:C1;2)) FALSE (B1, previously was TRUE) =ISTEXT(VLOOKUP(1;A1:C1;3)) FALSE (C1) =ISBLANK(B1) =ISBLANK(C1) =ISBLANK(VLOOKUP(1;A1:C1;2)) TRUE (B1, previously was FALSE) =ISBLANK(VLOOKUP(1;A1:C1;3)) FALSE (C1) Note that Microsoft Excel behaves different and always returns a number as the result of a reference to an empty cell or a formula cell with the result of an empty cell. For example: Case Formula Results and comments A1: <Empty> B1: =A1 Displays 0, but is just a reference to an empty cell. =ISNUMBER(A1) =ISTEXT(A1) =A1=0 =A1="" =ISNUMBER(B1) FALSE (Microsoft Excel: TRUE) =ISTEXT(B1) =B1=0 =B1="" TRUE (Microsoft Excel: FALSE) C1: =VLOOKUP(...) with empty cell result displays empty (Microsoft Excel: displays 0) =ISNUMBER(VLOOKUP(...)) =ISTEXT(VLOOKUP(...)) =ISNUMBER(C1) FALSE (Microsoft Excel: TRUE) =ISTEXT(C1)