0

I'm trying to use a custom number format to format a cell with a value that is rounded to zero (but not quite zero) to be blank.

The format string I'm using currently testing is #.###;-#.###;, although that doesn't quite work (shown below with "x" used in the third block of the format string). I'd want -0.234 to be shown as blank, not as a cell containing only a minus sign.

enter image description here

What would be the proper way to achieve this, given that I need/want to set the number format externally (via another application)?

Improve this question
1
  • I was able to do what you're asking (I think) using a format of #,###;;x
    – Bandersnatch
    Commented Oct 6, 2017 at 16:12

1 Answer 1

Reset to default
0

Each personalized format may have up to four sections, in the order:

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

So, if you want a blank on negative numbers and an x in zero numbers, just type in:

#,###; #,###; ;x

(just check the comma/dot and the semicolon/comma for the regional language of your computer)

Also, have in mind that the number sign # displays only the significant digits in the number, whereas the the zero 0 displays also non-significant zeros. Just whatever fits best to you.

Improve this answer
6
  • That unfortunately also leaves other negative numbers blank (-42, for example, should show up as -42).
    – mabako
    Commented Oct 9, 2017 at 6:53
  • then, put both the positive and negative numbers in the format. I edited my answer. check it
    – Thales
    Commented Oct 9, 2017 at 17:56
  • also, check if you want to display or not the minus sign before the negative number. If so, add the minus sign on the format: #,###; -#,###; ;x
    – Thales
    Commented Oct 9, 2017 at 17:58
  • I do want the minus sign shown before a negative number, but not before negative zero (a value less than zero, but greater than -0.5).
    – mabako
    Commented Oct 13, 2017 at 11:04
  • Then you need to format your numbers with Conditional formatting, because you want different criteria for the same type of number (i.e., for negative numbers, for example).
    – Thales
    Commented Oct 13, 2017 at 13:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .