I use a custom number format for numbers into the billions, which works as expected:

[<1000000]#,##0.0," K";  [<1000000000]#,##0.0,," M";  #,##0.0,,," B"

Now I need to deal with even larger numbers into the trillions, so I tried:

[<1000000000]#,##0.0," M"; [<1000000000000]#,##0.0,," B"; #,##0.0,,," T"

However, this fails - it displays one trillion (1,000,000,000,000,000) as 1,000,000.0 T, which is actually formatted as billions, not trillions.

I've checked for solutions, eg StackOverfow and googled as well, but I can't see why this formatting won't work. NOTE that the suggested SO (linked) format does NOT WORK.

Just trying to avoid slow custom functions etc, so if anyone has a suggestion, it would be most welcome.

CodePudding user response：

Documentation excerpt:

The number format documentation says this about the , token:

If it appears between two digit characters (0, # or ?), then it renders the entire number with grouping separators (grouping by the thousands).

So, =TEXT(1000, "0,0") renders 1000 as 1,000(It just adds a , every 3 digits)

If it follows the digit characters, it scales the digits by one thousand per comma (e.g., the format #0.0,, renders the number 12,200,000 as 12.2).

So, =TEXT(1000, "0,") would render 1000 as just 1.

Issue:

[<1,000,000,000]#,##0.0," M";
[<1,000,000,000,000]#,##0.0,," B";
 #,##0.0,,," T"

Only a single , is used for " M"(less than 1 Billion) formatting. So, the number is only scaled by 1000 instead of 1000^2.

Solution:

Add a another ,