New Excel Forum

This forum has been moved to

Ask all future questions in the New Excel Forum.


Use of "" (nullstring)

Free Excel Tips and Tutorials

Use of "" (nullstring)

Postby Sisyphus » Sat Feb 18, 2017 1:24 am

=IF(A1="","",100) - presume this formula to reside in B1.
This is a common test. The formula will check if A1 is blank and enter a nullstring in B1 or the number 100. The impression given is that if A1 is blank B1 will also be blank. This isn't true, however. The formula will enter a nullstring in B1 which appears as blank. In fact, B1 will contain a string (of no length), which is not a number and will lead to errors when the attempt is made to do calculations with it. It is better not to have strings in a worksheet designed to calculate numbers.
Now, [C1] =IF(B1="","",200) appears to do the same thing, but that isn't quite true either. The test IF(B1,"" will return True whether B1 contains a nullstring or is actually blank. The formula =A1*10 will return 0 because A1 is blank, but =B1*10 will return a #VALUE error because B1 holds a nullstring which can't be used to do calculations.
Therefore not using "" to either test or produce a blank cell is the way to go. Use zero instead, both for the test and the result, like, =IF(SUM(A1),100,0) or =IF(SUM(A1)=0,0,100)
SUM(A1) will return zero for any non-numeric value, including blanks, spaces or nullstrings. The test IF(SUM(A1) will return True for any value other than zero, regardless of how that zero came about.
However, this method will enter a zero whereas "" will deliver a blank cell. If a blank cell is required, Excel has a setting to suppress the display for zeroes. Remove the checkmark against the following option.
File/Options/Advanced ->Display options for this worksheet =>Show a zero in cells which have zero value
Have a great day! :D

I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Former Moderator
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Tips and Tutorials

Who is online

Users browsing this forum: No registered users and 8 guests