CODEWITHSUNDEEP
excelformattingnumbersexcel-2016
pAt84
pAt84

Reputation: 213

Number Formatting to 10K 5M, etc in Excel 2016

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

Upvotes: 1

Views: 8714

Answers (2)

nbctcp
nbctcp

Reputation: 33

in my case to simplify resistor value

=IF(B1>=1000000; TEXT(B1/1000000; "0.0") & "M"; IF(B1>=1000; IF(MOD(B1;1000)=0;TEXT(B1/1000; "0") & "k";TEXT(B1/1000; "0.#") & "k"); B1))

Resistor k M converter

Upvotes: 0

Axel Richter
Axel Richter

Reputation: 61852

In the user defined number format

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

the comma , means the thousands delimiter.

So if you get 2000,K as the result, then your thousands delimiter is not comma but another character in your locale settings. In German locale the thousands delimiter is dot . for example, so the format must be:

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

To be clear: The number format will change if you are changing the thousands delimiter after the format is applied. But the initially input must be done using the correct thousands delimiter at this time.

Upvotes: 6

Related Questions