decimal places and text() formula

Question

When I use the text formula, salesforce doesn't respect my decimal place formatting. For example,

Field A has 4 places to the right of the decimal. It shows up nicely on the page as 0.4000

If I'm generating a complex string based on the field, I might want to have a formula field equal to:

"The price is $" & text(Field_a__c) & ", buddy."

Unfortunately, this produces, "The price is $.4, buddy."

Answer 1

Here's the trick. You have to

1. take the "whole numbers" portion
2. Round down
3. add the decimal places using text
4. subtract to get the decimal portion
5. multiply that by 10^[how many digits you want to force]
6. pad the left hand side of the digits after the decimal point with zeroes to add up to the number of decimal places you need

text(floor(Field_a__c)) &"."& lpad(text((Field_a__c-floor(Field_a__c))*100),2,"0")

In this example, you'll get output like "157.50" if your number is 157.5, "147.00" if your number is 147, and "127.01" if your number is 127.01.

Answer 2

Maybe not an answer for the question but my solution for similar issue when it was needed to have always two decimals:

  IF(
    NOT(CONTAINS(TEXT(ROUND((Field__c * 100), 2)), '.')),
    TEXT(ROUND((Field__c * 100), 2)) + '.00',
    IF(
      (BEGINS(TEXT(ROUND((Field__c * 100), 2)), '.')),
      ('0' + TEXT(ROUND((Field__c * 100), 2))),
    
      RPAD(
        TEXT(ROUND((Field__c * 100), 2)), 
        (FIND('.', TEXT(ROUND((Field__c * 100), 2))) + 2), 
        '0'
      )
    )
  ) + ' %'

If Decimal doesn't contain decimal places then we add '.00', else we check if begins with '.' (for example .5) then we add '0' prefix, in other case we add '0' to match 2 decimal places