Forum Discussion

DanielJohnson2's avatar
DanielJohnson2
Qrew Trainee
4 years ago

Formula to convert long numbers to short with abbreviation

Hi All,

In short, I need a formula to convert 123,000,000,000 to 123 B. I need it to cover all cases from 1,000,000 to 999,000,000,000,000.

If possible I would like 123,500,000,000 and 123,499,999,999 to both be 123.5, but for 123,400,000,000 to be 123.4 B. Since it's going to be a formula text field I thought maybe it would be easy to cover the case where if the right two characters are .0 then to slice them off.

Thoughts?

------------------------------
Daniel Johnson
------------------------------
Formulas and functions
  • DanielJohnson2's avatar
    DanielJohnson2
    Qrew Trainee
    4 years ago
    Here's what I've got so far. [AUM] is the entry field. I made two new fields, one a formula numeric field [AUM (short num)] that only shows 1 decimal:

    var number AUMlength = Length(ToText([AUM]));
    var number AUMdiv = $AUMlength/3;
    var number AUMrem = Rem($AUMlength,3);
    var number AUMshortA = [AUM]/10^(Int($AUMdiv)*3);
    var number AUMshortB = [AUM]/10^((Int($AUMdiv)-1)*3);

    Case($AUMrem,
    0, $AUMshortB,
    1, $AUMshortA,
    2, $AUMshortA)

    And the formula text field [AUM (short text)]:

    var number AUMlength = Length(ToText([AUM]));

    If(
    $AUMlength>=7 and $AUMlength<=9, "$" & ToText([AUM (short num)]) & " M",
    $AUMlength>=10 and $AUMlength<=12, "$" & ToText([AUM (short num)]) & " B",
    $AUMlength>=13 and $AUMlength<=15, "$" & ToText([AUM (short num)]) & " T")

    I'll have to give the rounding another think later, but this works so far.

    ------------------------------
    Daniel Johnson
    ------------------------------