Skip to main content

I’m trying to get text and currency to combine and show properly with 2 decimal places even if they are 0.00.

I have a product field (with text), a value field (with Currency)

If i did a simple Formula {Product} & “ - $” & {Value} the result makes $18.30 from {Value} turn into $18.3 and $20.00 turn into $20, removing any 0’s after the decimal.

Through research and help we can do the following equation:

CONCATENATE({Product}, " - $", IF({Value}, ROUND({Value}, 2) & IF(FIND(".", ROUND({Value}, 2) & " ") = LEN(ROUND({Value}, 2) & "") - 1, "0", ""), "-.--")," "&{/Unit})

This will put in 2 decimal places unless they are .00, then it reverts to suppressing the two 0’s after the decimal place, how do i get them back?

Hey ​@GegH,

If you do not care about commas for showing thousands, then you can use this formula:
 

{Product} & " - $" &
IF(
  {Value},
  IF(
    FIND(".", ROUND({Value},2)&""),
    ROUND({Value},2) &
    REPT("0", 2 - LEN(RIGHT(ROUND({Value},2)&"", LEN(ROUND({Value},2)&"") - FIND(".", ROUND({Value},2)&"")))),
    ROUND({Value},2) & ".00"
  ),
  "-.--"
) &
IF({Unit}, " " & {Unit}, "")

 

Mike, Consultant @ Automatic Nation 
YouTube Channel

Brilliant, perfect.

Something so simple, the problem not the solution, and i know it would bug me forever, it just simply doesn’t look right

Thankyou so much

Terms & ConditionsAccessibility statement