When a cell in Excel has not been populated, it contains no value. The Null() function returns an array with one element representing a missing value. Null() is therefore used by SubstituteArray() to populate empty cells with a specified value. In other words, Null() allows SubstituteArray() to recognise unpopulated cells in Excel.

Syntax

Null ()

Example. Recognising and replacing empty cells

The SubstituteArray() function is first used to replace any empty cells with the word ‘Weekend’. This function looks for empty strings (“”). Because the blank cells contain no values (are not empty strings), the function does not work. The second SubstituteArray() function looks for null values when making replacements. This function works because Null() allows it to recognise the blank cells.

Download this example