# Custom Number Format (hundred-thousands, Millions)

May 22, 2009I have a giant sheet that culminates in a graph. It is for various regions. When used in a country with in-control currency, you end up with a nice healthy number that looks good on the graph. When our Zimbabwe office uses it with their crazy inflation and high prices, we get a crazy number that goes into scientific notation...

Anyway, my question:

Given a number like 4,200,000 or 6,500 or 345,123, I want to be able to show as few trailing numbers as possible, and give a nice K or B or D or however you abbreviate Quadrillion (kM? for Zimbabwe) after it.

And, if possible, I'd like to do it with number formats, but I am flexible so long as it doesn't use VBA.

The lookup table would be:

Sheet1 AB1<1,000As is21,000K31,000,000Mil.41,000,000,000Bil.5>1,000,000,000,000Tril. Excel tables to the web >> Excel Jeanie HTML 4

All numbers have 2 significant digits, and I want to keep 2, so if it ends up as 4,200,000, I want the answer to read 4.2 Mil, etc.

I tried doing this with a lookup, and couldn't handle it.