How to format very large numbers?

[JenniferMurphy]

I need to format numbers from 0 to 10^24 or more. Here's what I'd like

0 - 999 -> as is
1,000 - 999,000 -> xxx thousand
1,000,000 - 999,000,000 -> xxx million
1,000,000,000 - 999,000,000,000 -> xxx billion
... and so on

Is there a formatting string that will do that?

Is there a built-in function that will convert the number into that string or does anyone have a UDF that will do that.

I don't care whether the result is a number or a string.

 

[JenniferMurphy]

You could use this to build a formula like this, (so you have an alternative to a UDF):

You can update the arrays to your needs. Or use a helper table in a hidden sheet for example with the {"+0";"+3";"+6";"+9";"+12";"+15";"+18";"+21";"+24"},{"";"Thousand";"Million";"Billion";"Trillion";"Quadrillion";"Quintillion";"Sextillion";"Septillion"} values

Wow!

That's a very clever alternative. I'm going to see if I can implement the helper table you suggested.

 

[felixstraube]

Thanks for the feedback. Happy to help.