Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Shannon Andreas 21Shannon 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

 
Best Answer chosen by Shannon Andreas 21
Parker EdelmannParker Edelmann
If 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 SharmaRavi Dutt Sharma
Hey Shannon,
Why are you storing it as text and not a number?
Shannon Andreas 21Shannon Andreas 21
Kind 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 SharmaRavi Dutt Sharma
Anyways you can use below formula if you want to store it as text 
LEFT(TEXT(FLOOR( Lease_PB_Total_Price__c  / 1000000)),LEN(TEXT(FLOOR( Lease_PB_Total_Price__c  / 1000000)))-3)

I have done it for the first IF condtion, you can apply it for others also
Shannon Andreas 21Shannon Andreas 21
I started playing around with that, but couldn't get the right combination of things correct!! Thanks!
Ravi Dutt SharmaRavi Dutt Sharma
Please mark the question as solved if you got the answer that you were looking for. Thanks.
Shannon Andreas 21Shannon Andreas 21
So 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 EdelmannParker Edelmann
If 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
This was selected as the best answer
Parker EdelmannParker Edelmann
For 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 21Shannon Andreas 21
You 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 EdelmannParker Edelmann
That'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 21Shannon Andreas 21
I think deleting that line was the only thing I didn't try!! I was wracking my brain all day!

Thanks again.
Parker EdelmannParker Edelmann
No 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.