function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Shannon Andreas 21Shannon Andreas 21 

Need help with conversion of number to text and adding commas

I have this lovely formula field:

"$" & if(TotalPrice<1000,text(round(TotalPrice,2)), 
text(floor(TotalPrice/1000))&","&right(text(floor(TotalPrice)),3))

That does a great job. It takes a number value and makes it text. It also adds a "," and a "$". It works great for any "numbers" from 1000 to 999999. Over a million, not so much. We obviously have to add another ",".

My thought is that we need another if statement, but I cannot seem to get one to work. Can anyone assist?

The end result is anything from 1000-999999 we need one comma. Anything over 1000000, we need two.

Thanks a million!

Shanno9n
Best Answer chosen by Shannon Andreas 21
Shannon Andreas 21Shannon Andreas 21
Thanks Parker! I got a great resolution with this one:

IF( 
  TotalPrice >= 1000000, 
  TEXT(FLOOR(TotalPrice / 1000000)) & ",", 
  "") & 
IF( 
  TotalPrice >= 1000, 
  RIGHT(TEXT(FLOOR(TotalPrice / 1000)), 3) & ",", 
  "") & 
RIGHT(TEXT(FLOOR(TotalPrice)), 3) & "." & 
IF( 
  MOD(TotalPrice , 1) * 100 < 10, 
  "0" & TEXT(ROUND(MOD(TotalPrice , 1), 2) * 100), 
  TEXT(MIN(ROUND(MOD(TotalPrice , 1), 2) * 100, 99)) 
)

All Answers

Parker EdelmannParker Edelmann
Yes, use an IF statement on the else result. It should look something like this: 
"$" & if(TotalPrice<1000,text(round(TotalPrice,2)), 
IF(TotalPrice<1000000,text(floor(TotalPrice/1000))&","&right(text(floor(TotalPrice)),3),
/* However you'd like to display +$1000000 values goes here*/))
Hope this helps you,

Parker
 
Shannon Andreas 21Shannon Andreas 21
Thanks Parker! I got a great resolution with this one:

IF( 
  TotalPrice >= 1000000, 
  TEXT(FLOOR(TotalPrice / 1000000)) & ",", 
  "") & 
IF( 
  TotalPrice >= 1000, 
  RIGHT(TEXT(FLOOR(TotalPrice / 1000)), 3) & ",", 
  "") & 
RIGHT(TEXT(FLOOR(TotalPrice)), 3) & "." & 
IF( 
  MOD(TotalPrice , 1) * 100 < 10, 
  "0" & TEXT(ROUND(MOD(TotalPrice , 1), 2) * 100), 
  TEXT(MIN(ROUND(MOD(TotalPrice , 1), 2) * 100, 99)) 
)
This was selected as the best answer
Parker EdelmannParker Edelmann
Awesome, glad you could make it work. That version is definately more efficient than the one I gave you.