You need to sign in to do that

Don't have an account?

Shannon Andreas 21

# Text Formula - remove ".00"

Hello!

Here is the formula I am using:

"$" & IF(

Lease_PB_Total_Price__c >= 1000000,

TEXT(FLOOR( Lease_PB_Total_Price__c / 1000000)) & ",",

"") &

IF(

Lease_PB_Total_Price__c >= 1000,

RIGHT(TEXT(FLOOR(Lease_PB_Total_Price__c / 1000)), 3) & ",",

"") &

RIGHT(TEXT(FLOOR(Lease_PB_Total_Price__c)), 3) & "." &

IF(

MOD(Lease_PB_Total_Price__c , 1) * 100 < 10,

"0" & TEXT(ROUND(MOD(Lease_PB_Total_Price__c , 1), 2) * 100),

TEXT(MIN(ROUND(MOD(Lease_PB_Total_Price__c , 1), 2) * 100, 99))

)

Here is the result:

$30,628.00

I want to lose the ".00"

I tried rounding but do not think I can round with text?

Please help and thanks!

Shannon

Here is the formula I am using:

"$" & IF(

Lease_PB_Total_Price__c >= 1000000,

TEXT(FLOOR( Lease_PB_Total_Price__c / 1000000)) & ",",

"") &

IF(

Lease_PB_Total_Price__c >= 1000,

RIGHT(TEXT(FLOOR(Lease_PB_Total_Price__c / 1000)), 3) & ",",

"") &

RIGHT(TEXT(FLOOR(Lease_PB_Total_Price__c)), 3) & "." &

IF(

MOD(Lease_PB_Total_Price__c , 1) * 100 < 10,

"0" & TEXT(ROUND(MOD(Lease_PB_Total_Price__c , 1), 2) * 100),

TEXT(MIN(ROUND(MOD(Lease_PB_Total_Price__c , 1), 2) * 100, 99))

)

Here is the result:

$30,628.00

I want to lose the ".00"

I tried rounding but do not think I can round with text?

Please help and thanks!

Shannon

Parker EdelmannIf you get rid of the underlined portion of the formula, you won't have any decimal, but it won't round up:

"$" & IF(

Lease_PB_Total_Price__c >= 1000000,

TEXT(FLOOR( Lease_PB_Total_Price__c / 1000000)) & ",",

"") &

IF(

Lease_PB_Total_Price__c >= 1000,

RIGHT(TEXT(FLOOR(Lease_PB_Total_Price__c / 1000)), 3) & ",",

"") &

RIGHT(TEXT(FLOOR(Lease_PB_Total_Price__c)), 3) & "." &

IF(MOD(Lease_PB_Total_Price__c , 1) * 100 < 10,

"0" & TEXT(ROUND(MOD(Lease_PB_Total_Price__c , 1), 2) * 100),

TEXT(MIN(ROUND(MOD(Lease_PB_Total_Price__c , 1), 2) * 100, 99))

)

If you still want that functionality in the formula, you may be able to use this:

IF(

Lease_PB_Total_Price__c >= 1000000,

TEXT(FLOOR( Lease_PB_Total_Price__c / 1000000)) & ",",

"") &

IF(

Lease_PB_Total_Price__c >= 1000,

RIGHT(TEXT(FLOOR(Lease_PB_Total_Price__c / 1000)), 3) & ",",

"") &

RIGHT(TEXT(FLOOR(Lease_PB_Total_Price__c)), 3) &

IF(VALUE(RIGHT(TEXT(Lease_PB_Total_Price__c),2)) = 0,"." & IF(

MOD(Lease_PB_Total_Price__c , 1) * 100 < 10,

"0" & TEXT(ROUND(MOD(Lease_PB_Total_Price__c , 1), 2) * 100),

TEXT(MIN(ROUND(MOD(Lease_PB_Total_Price__c , 1), 2) * 100, 99))

), "")

Notice the differences in the two underlined blocks and the line above them. It may not work exactly right, so you may need to tweek it.

Thanks,

Parker

## All Answers

Ravi Dutt SharmaHey Shannon, Why are you storing it as text and not a number?

Shannon Andreas 21Kind of a long story to type here...it just needs to be in text format to output to something else.

Can you help?

Ravi Dutt SharmaAnyways you can use below formula if you want to store it as text
I have done it for the first IF condtion, you can apply it for others also

Shannon Andreas 21I started playing around with that, but couldn't get the right combination of things correct!! Thanks! Ravi Dutt SharmaPlease mark the question as solved if you got the answer that you were looking for. Thanks. Shannon Andreas 21So the 1000 line is a little different as it has a RIGHT function as well. Where would I place the LEFT function you gave me? Same question for the >10 line as well?

Do I put it before the RIGHT function like this? Doesn't seem to like that though:

IF(

TotalPrice >= 1000,

LEFT(TEXT(FLOOR( TotalPrice / 1000)), LEN(TEXT(FLOOR(TotalPrice / 1000)))-3)&

RIGHT(TEXT(FLOOR(TotalPrice / 1000)), 3) & ",",

"") &

RIGHT(TEXT(FLOOR(TotalPrice)), 3)

This way didn't seem to do anything at all?

Parker EdelmannIf you get rid of the underlined portion of the formula, you won't have any decimal, but it won't round up:

"$" & IF(

Lease_PB_Total_Price__c >= 1000000,

TEXT(FLOOR( Lease_PB_Total_Price__c / 1000000)) & ",",

"") &

IF(

Lease_PB_Total_Price__c >= 1000,

RIGHT(TEXT(FLOOR(Lease_PB_Total_Price__c / 1000)), 3) & ",",

"") &

RIGHT(TEXT(FLOOR(Lease_PB_Total_Price__c)), 3) & "." &

IF(MOD(Lease_PB_Total_Price__c , 1) * 100 < 10,

"0" & TEXT(ROUND(MOD(Lease_PB_Total_Price__c , 1), 2) * 100),

TEXT(MIN(ROUND(MOD(Lease_PB_Total_Price__c , 1), 2) * 100, 99))

)

If you still want that functionality in the formula, you may be able to use this:

IF(

Lease_PB_Total_Price__c >= 1000000,

TEXT(FLOOR( Lease_PB_Total_Price__c / 1000000)) & ",",

"") &

IF(

Lease_PB_Total_Price__c >= 1000,

RIGHT(TEXT(FLOOR(Lease_PB_Total_Price__c / 1000)), 3) & ",",

"") &

RIGHT(TEXT(FLOOR(Lease_PB_Total_Price__c)), 3) &

IF(VALUE(RIGHT(TEXT(Lease_PB_Total_Price__c),2)) = 0,"." & IF(

MOD(Lease_PB_Total_Price__c , 1) * 100 < 10,

"0" & TEXT(ROUND(MOD(Lease_PB_Total_Price__c , 1), 2) * 100),

TEXT(MIN(ROUND(MOD(Lease_PB_Total_Price__c , 1), 2) * 100, 99))

), "")

Notice the differences in the two underlined blocks and the line above them. It may not work exactly right, so you may need to tweek it.

Thanks,

Parker

Parker EdelmannFor the first section of my advice, the part about eliminating the decimal altogether, you'll actually need to remove the decimal; I forgot to do that.

... RIGHT(TEXT(FLOOR(Lease_PB_Total_Price__c)), 3) /*Remove that ->

*/ & ....& "."Shannon Andreas 21You never fail me Parker!! I know this is at least the 2nd time you have helped me with success!!

I used the second one. I did not want the decimal.

Thanks!!

Shannon

Parker EdelmannThat's what I'm here for. I knew I had seen the formula before, and your name was familiar. Glad I could guide you to success this time as well.

Thanks again,

Parker.

Shannon Andreas 21I think deleting that line was the only thing I didn't try!! I was wracking my brain all day!

Thanks again.

Parker EdelmannNo problem. One time someone was building a flow, and all that was needed was a "none of the above" checkbox. Sometimes it just takes another pair of eyes to suggest a slight tweak.