Format numbers as billions / millions / thousands, smartly [duplicate]

Question

Any recipes out there for a "smart" number formatting formula that's "scale-aware," a la Rails ActionView's distance_of_time_in_words method?

I would expect to enter a number like: 1,816,724 and see 1.8M (or enter 2,394 and see 2.4K).

Answer 1

To format conditionally using Billions / Millions / Thousands in Google Spreadsheets or Excel, you can use:

[<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B"

To apply this setting in Google sheets, goto Format > Number > Custom Number Format and copy paste the above format string.

To learn more about formatting options, go to https://support.google.com/docs/answer/56470#zippy=%2Ccustom-number-formatting

Answer 2

Select the cells to receive a custom number format and tap Ctrl+1. When the Format Cells dialog opens, go to the Number tab and choose Custom from the list down the left side. Supply the following for the Type:,

[>999999]0.0,,\M;[>999]0.0,K;0

Note that M is a reserved character in a format mask and must be escaped with a backslash to become literal. You can also wrap it in quotes but since I use custom number formats in VBA's .NumberFormat property, I prefer the escape character to avoid having to deal with quotes within quoted strings. Your results should resemble the following: