Custom Number Format for Thousands, Millions, Billions, AND Trillions-CodePudding

I've looked everywhere and haven't found any solutions to getting numbers in the "Trillions" to format with a trailing "T".

Here is the custom number format I'm currently using: [<999950]$0.00,"K";[<999950000]$0.00,,"M";$0.00,,,"B"

Which displays these numbers as so:

Is Google Sheets also able to make numbers in the Trillions format as $1.00T?

Thanks!

CodePudding user response：

not possible. this "internal" formatting is by default able to work with only 3 types of numbers:

positive (1, 2, 5, 10, ...)
zero (0)
negative (-3, -9, -7, ...)

this can be somehow tweaked to show custom formatting like K, B, M but you always got only 3 slots you can use, meaning that you can't have trillions as the 4th type/slot

however, this would cover your needs:

=ARRAYFORMULA(IF(ABS(A:A)<10^3, A:A&"", 
 IF(1*ABS(A:A)<10^6,  TEXT(A:A/10^3,  "#.0\k"),
 IF(1*ABS(A:A)<10^9,  TEXT(A:A/10^6,  "#.0\M"),
 IF(1*ABS(A:A)<10^12, TEXT(A:A/10^9,  "#.0\B"),
 IF(1*ABS(A:A)<10^15, TEXT(A:A/10^12, "#.0\T"),
 IF(1*ABS(A:A)<10^18, TEXT(A:A/10^15, "#.0\Q\a"),
 IF(1*ABS(A:A)<10^21, TEXT(A:A/10^18, "#.0\Q\i"),
 IF(1*ABS(A:A)<10^24, TEXT(A:A/10^21, "#.0\S\x"),
 IF(1*ABS(A:A)<10^27, TEXT(A:A/10^24, "#.0\S\p"),
 IF(1*ABS(A:A)<10^30, TEXT(A:A/10^27, "#.0\O"),
 IF(1*ABS(A:A)<10^33, TEXT(A:A/10^30, "#.0\N"),
 IF(1*ABS(A:A)<10^36, TEXT(A:A/10^33, "#.0\D"),
 IF(1*ABS(A:A)<10^39, TEXT(A:A/10^36, "#.0\U"),
 IF(1*ABS(A:A)<10^42, TEXT(A:A/10^39, "#.0\D\d"),
 IF(1*ABS(A:A)<10^45, TEXT(A:A/10^42, "#.0\T\d"),
 IF(1*ABS(A:A)<10^48, TEXT(A:A/10^45, "#.0\Q\a\d"),
 IF(1*ABS(A:A)<10^51, TEXT(A:A/10^48, "#.0\Q\u\d"),
 IF(1*ABS(A:A)<10^54, TEXT(A:A/10^51, "#.0\S\x\d"),
 IF(1*ABS(A:A)<10^57, TEXT(A:A/10^54, "#.0\S\p\d"),
 IF(1*ABS(A:A)<10^60, TEXT(A:A/10^57, "#.0\O\d"),
 IF(1*ABS(A:A)<10^63, TEXT(A:A/10^60, "#.0\N\d"),
 IF(1*ABS(A:A)<10^66, TEXT(A:A/10^63, "#.0\V"),
 IF(1*ABS(A:A)<10^69, TEXT(A:A/10^66, "#.0\C"), ))))))))))))))))))))))))