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.
 824 Points
Posted 3 years ago
Ⲇanom the ultimate (Dan Diebolt), Champion
 30,224 Points
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.
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.
 824 Points
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.
Ⲇanom the ultimate (Dan Diebolt), Champion
 30,224 Points
Related Categories

Forms
 3354 Conversations
 150 Followers

Formulas & functions
 3060 Conversations
 84 Followers

Tables & fields
 7345 Conversations
 201 Followers