 ShowAll Questionssorted byDate Posted Shannon 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 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))
) Parker 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 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 Edelmann
Awesome, glad you could make it work. That version is definately more efficient than the one I gave you.