Custom Number Format for scale-aware number formatting in Google Sheets (e.g. 1,200→ 1.2k; 1,200,000 → 1.2M; 1,200,000,000 → 1.2B)

Question

Does anyone know a generalized Custom Number Format I can apply in Google Sheets such that numbers appear with it's scale operator and rounded to one decimal place?

Example:

I looked up a good answer below but I'm not sure how to edit the formula for billions, trillions, and so on.

Formula for smart, scale-aware number-formatting in Google Sheets? (e.g. 1,024 → 1k, 1,816,724 → 1.8M)

Answer 1

Unfortunately the partial solution found does not appear to be extendable beyond two numeric ranges, so as an alternative there is an extendable but not generalised solution that converts integers greater than 999 to text in order to display as required up to trillions (short scale) but not quadrillion or above (though more terms may be added):

=if(A1<1000,A1,if(A1<1000000,text(A1/1000,"#.0")&"K",if(A1<1000000000,text(A1/1000000,"#.0")&"M",if(A1<1000000000000,text(A1/1000000000,"#.0")&"B",text(A1/1000000000000,"#.0")&"T"))))