Google sheetsFormat numbers as billions / millions / thousands, smartly

Question

I have this template format for the numbers:

[>=999950]0.0,,"M";[<=-999950]0.0,,"M";0.0,"K"

But how can I do to make Zeros show as " - " ?

Answer 1

I can suggest that you're looking for something like this

[>=999950]0.0,,"M";[<=-999950]0.0,,"M";#,,"-"

Updated

As @Benoît Wéry said it's not possible. But I think you can.

I'm using a simple script for build a good view

function formatToMKZeros(range) {
  var values = range.getValues();
  return range.setNumberFormats(
    range.getNumberFormats().map(function(row, r) {
      return row.map(function(format, c) {
        return isNumeric(values[r][c])
          ? values[r][c] !== 0 && Math.abs(values[r][c]) < 999950
            ? '[<999950]0.0,"K";[>-999950]0.0,"K"'
            : '[>=999950]0.0,,"M";[<=-999950]0.0,,"M";#,,"-"'
          : format;
      });
    })
  );
}

• The full code
• The great article about formats Handling Negative Numbers in a Complex Custom Format
• Also simple samples The Only Spreadsheet Custom Number Format You’ll Ever Need

Answer 2

I don't think you can:

Conditions can only be applied to the first two sub-formats and if a number matches more than one, it will use the first one it matches. If there is a third format, it will be used for "everything else", otherwise if a number doesn’t match either format, it will be rendered as all "#"s filling up the cell width. The fourth format is always used for text, if it exists.

Source: https://developers.google.com/sheets/api/guides/formats#meta_instructions