Format $24,100,000 to display $24.1M.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I would like to create a text formula field that will convert $24,100,000 to display $24.1M or $500,000 to $500K.

Thanks for any advice.

Photo of rocketc

rocketc

  • 774 Points 500 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,004 Points 20k badge 2x thumb
Posted in wrong question!

Try this (not fully tested):

If(0 <= [N] and [N] < 1000, ToString([N]),
  If(1000 <= [N] and [N] < 1000000, ToString(Int(0.5 + [N]/1000)) & "K",
    If(1000000 <= [N] and [N] < 1000000000, ToString(Int(0.5 + [N]/1000000)) & "M",
      If(1000000000 <= [N] and [N] < 1000000000000, ToString(Int(0.5 + [N]/1000000000)) & "G",
         If(1000000000000 <= [N] and [N] < 1000000000000000, ToString(Int(0.5 + [N]/1000000000000)) & "T"
        )
      )
    )
  )
)

I am pretty sure the above formula is wrong in terms of the rounding up but I am too tired to fix it now. 
Photo of rocketc

rocketc

  • 774 Points 500 badge 2x thumb
Dan, thank you sir. Works great. Not sure how to get the decimal to show up for the millions....if I have 24,100,000...I would like for it to show 24.1M....if is showing up as 24M.....also would to remove decimals for numbers under 1000.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,004 Points 20k badge 2x thumb