2 Replies Latest reply on Aug 23, 2011 12:25 PM by joedu

# Formatting numbers to American short form

### Title

Formatting numbers to American short form

### Post

I've searched and have not found an discussion on formatting numbers to the "American short form." Basically this takes a number like "32345700" and returns "32.3 million". Seems like there should be either a custom function or script for this, but haven't come across it. Any help?

• ###### 1. Re: Formatting numbers to American short form

Nevermind! Got it.... simple, and overthought, as usual ;)

"\$" &

Case (

Length (Number) =7 ; Round ( Number ; -4 ) / 1000000;
Length (Number) =8 ; Round ( Number ; -5 ) / 1000000;
Length (Number) =9 ; Round ( Number ; -6 ) / 1000000;
Length (Number) =10 ; Round ( Number ; -7 ) / 1000000000;
Length (Number) =11 ; Round ( Number ; -8 ) / 1000000000;
Length (Number) =12 ; Round ( Number ; -9 ) / 1000000000;

)

&

Case (

Length (Number) > 6 and Length (Number) <10 ; " million" ;
Length (Numbert) > 9 and Length (Number) <13 ; " billion" ;
Length (Number) > 12 and Length (Number) <16 ; " trillion"
)

Cheers!

• ###### 2. Re: Formatting numbers to American short form

As a quick follow-up, the rounding amount (-4, -5, etc) dictates the number of decimals. I needed to have only 1 place after the decimal in every instance, so in the millions, the rounding had to be -5, and for billions, -8, etc. Thus the calculation could be simplified to just this:

++++++

"\$" &

Case (

Length (Number) > 6 and Length (Number) < 10 ; Round ( Number ; -5 ) / 1000000;

Length (Number) > 9 and Length (Number) < 13 ; Round ( Number ; -8 ) / 1000000000;

Length (Number) > 12 and Length (Number) < 16 ; Round ( Number ; -8 ) / 1000000000000;

)

&

Case (

Length (Number) > 6 and Length (Number) <10 ; " million" ;
Length (Numbert) > 9 and Length (Number) <13 ; " billion" ;
Length (Number) > 12 and Length (Number) <16 ; " trillion"
)

+++++++

So,

The number "1234567" results in "\$1.2 million"

The number "12345678" results in "\$12.3 million"

Etc. It will round up so that if the 2nd place after the decimal is a 5 or larger, the first decimal place will round up. So 1250000 would result in "\$1.3 million."