Google Sheets Custom Number Format - Unchanging #<1000

Question

I am well aware of the excellent Custom Number Formula:

[<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B"

Which abbreviates #,000 numbers to #K, #,000,000 to #M etc...

However, I am looking for a formula that will leave numbers below 1000 unchanged.

For example, the number 999 will remain 999 but the number 1,001 will abbreviate to 1K.

user0 · Accepted Answer · 2019-03-15 20:03:58Z

2

answered Mar 15 '19 at 20:03

user0

23.2k8 gold badges39 silver badges64 bronze badges

the only question is... how can i add the [<1000]0""; to the existing formula [<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B" to give a complete solution? – Elad Ratson Mar 18 '19 at 9:16
I tried [<1000]0"";[<100000]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B" but for some reason I don't understand it doesn't work :-( – Elad Ratson Mar 18 '19 at 9:17
1

that won't do, this is the only way how you can have this working [<1000]0"";[<1000000]0,"K" custom internal formattings are not so flexible – user0 Mar 18 '19 at 11:42

Add a comment |

1 Answer 1