Download Excel File: https://excelisfun.net/files/EMT1764.xlsx
This video exposes the misleading Microsoft terminology for ‘Blanks’. This video examines empty cells, cells with double quotes (zero length text strings) and all the ramifications for Excel worksheet formulas. See the functions: ISTEXT, ISBLANK, COUNTBLANKS, LEN, AND, SUMIFS and COUNTIFS. Learn about the features Filter and Paste Special Skip Blanks. Learn about logical formulas and comparative operators to deal with empty cells, zero values and zero length text strings.
(00:00) Introduction
(00:36) Look at empty cells and zero length text strings and a space.
(01:18) In a formula, an empty cell always evaluates to zero.
(01:39) Empty cell and zero compared with equal sign is TRUE.
(02:00) A ‘zero length text string’, “”, is text with zero length.
(02:04) To check if zero length text string is text, use ISTEXT function.
(02:32) To check if zero length text string has zero length, use LEN (length) function.
(02:50) To check if zero length text string is both text and zero length, use: AND function
(03:18) Text can never equate to a number.
(02:30) Text is not equal to an empty cell. But, with a comparative operator, they are!?!?!?
(04:06) ‘Null text string’ or ‘blank’ are not accurate descriptions of a ‘zero length text string’.
(04:23) Microsoft helps to cause this confusion with Help:
(04:28) ISBLANK function: Blank = “empty cell”. Function checks for empty cell.
(04:50) COUNTBLANK function: Blank = “empty cell or zero length text string”.
(05:18) Paste Special ‘Skip Blanks’: Blank = “empty cell”.
(06:09) Filter: Blank = “empty cell or zero length text string or space”.
(06:44) Criteria in SUMIFS, COUNTIFS and the like: 1) “NOT” will return a TRUE for any cell that is NOT “empty”. “=” will return a TRUE for any cell that is “empty”.
(07:46) Logical formulas.
(08:00) The Logical Test: C37=””, asks: “Is cell empty or Zero Length Text String”
(08:09) The Logical Test: C37=NOT””, asks: “Is cell NOT empty or NOT Zero Length Text String”
(08:33) Formula to check for empty cell.
(09:08) Formula to check if cell is not empty
(09:27) Summary of video.
(09:56) Closing and video links