You need to sign in to do that

Don't have an account?

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

"$" & 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

Shannon Andreas 21Thanks 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 EdelmannYes, use an IF statement on the else result. It should look something like this:

Hope this helps you,Parker

Shannon Andreas 21Thanks 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 EdelmannAwesome, glad you could make it work. That version is definately more efficient than the one I gave you.