CODEWITHSUNDEEP
excelformattingexcel-formula
trailblazer
trailblazer

Reputation: 1451

Custom number format for NULL

I have an excel sheet that I populate from a database. What is the custom number format that I can use to replace NULL values in the excel by a hyphen '-' ? I know that I can use the following formula

#,##0_);(#,##0);–_);_(@_)

in order to display it for a zero. But I don't know how to handle a null value.

Upvotes: 0

Views: 8871

Answers (1)

SheetJS
SheetJS

Reputation: 22905

The simple answer is that the number format strings do not have a section for NULL values. See ECMA-376 section 18.8.31 for an explanation:

Up to four sections of format codes can be specified. The format codes, separated by semicolons, define the formats for positive numbers, negative numbers, zero values, and text, in that order. If only two sections are specified, the first is used for positive numbers and zeros, and the second is used for negative numbers. If only one section is specified, it is used for all numbers.

One workaround is to check for null values, emit a text value, and set the text format. For example, using the formula 0;0;0;"-":

VALUE    FORMATTED
#NULL!   -
NULL     -
1        1
2        2
3        3
4        4

Note that this only works if you expect the result to be numerical. If you expect a string value, you can do the opposite ("-";"-";"-";@)

Upvotes: 1

Related Questions