Number Formatting to 10K 5M etc in Excel 2016

Question

I want to do something fairly simple in Excel 2016 but seem to be running into a wall. The idea is to convert numbers like this

1 -> 1
10 -> 10
100 -> 100
1000 -> 1K
10000 -> 10K
100000 -> 100K
1000000 -> 1M
....

I got the following number format:

[>=1000000]0,,"M";[>=1000]0,"K";0

But what I get for 20000 is

20000,K

while I was expecting

20K

I think this is fairly simple and I just made a rookie mistake. Anyone?

Thank you! Pat

Answer 1

In the format of a user-defined number

[>=1000000]0,,"M";[>=1000]0,"K";0

The thousands delimiter is represented by the comma.

So, if you obtain 2000,K, your thousands delimiter isn't a comma, but rather a different character in your locale settings. For example, the thousands delimiter in German locale is dot, hence the format must be:

[>=1000000]

0.."M";[>=1000]

0."K";0

To be clear, if you modify the thousands delimiter after the format has been applied, the number format will change. However, the initial input must be done with the right thousands delimiter.