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

Forms
 3270 Conversations
 134 Followers

Formulas & functions
 2921 Conversations
 74 Followers

Tables & fields
 7209 Conversations
 182 Followers