+ Start a Discussion
Vickie SmithVickie Smith 

Currency on a TEXT field

I have a custom data type formula field that contains a currency. See below... 

Name + "  $" +  TEXT(Quote_Per_Adult__c) + " rate per person based on " +  TEXT(Occupancy__c) + " Occupancy  "

It will populate in the email template like this...

Golden Nugget:Carson Tower $502.3 rate per person based on Double Occupancy

How can I make the price populate with the decimals at the end? instead of $502.3, I need it to populate as $502.30

I've tried just typing in Currency(Quote_Per_Adult__c), that didn't work.....I also tried TEXT(Quote_Per_Adult__c |# Currency) and it also did not like that.

Any help please!
Best Answer chosen by Vickie Smith
Ankit SehgalAnkit Sehgal
Try this formula for currency to text conversion:

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

All Answers

Ankit SehgalAnkit Sehgal
Try this formula for currency to text conversion:

text(floor(Quote_Per_Adult__c)) &"."& lpad(text((Quote_Per_Adult__c-floor(Quote_Per_Adult__c))*100),2,"0")
This was selected as the best answer
Vickie SmithVickie Smith
That worked amazingly! Thank you so much!
Vickie SmithVickie Smith
Is there a way (in the formula you provided, to include a comma in the number($2,011.00)?
Ankit SehgalAnkit Sehgal
Yes.
Though the formula would be a bit long :D

CASE(LEN(TEXT(Quote_Per_Adult__c)),
1, TEXT(Quote_Per_Adult__c),
2, TEXT(Quote_Per_Adult__c),
3, TEXT(Quote_Per_Adult__c),
4, LEFT(TEXT(Quote_Per_Adult__c), 1) & "," & RIGHT(TEXT(Quote_Per_Adult__c), 3),
5, LEFT(TEXT(Quote_Per_Adult__c), 2) & "," & RIGHT(TEXT(Quote_Per_Adult__c), 3),
6, LEFT(TEXT(Quote_Per_Adult__c), 3) & "," & RIGHT(TEXT(Quote_Per_Adult__c), 3),
7, LEFT(TEXT(Quote_Per_Adult__c), 1) & "," & MID(TEXT(Quote_Per_Adult__c), 2,3) & "," & RIGHT(TEXT(Quote_Per_Adult__c), 3),
8, LEFT(TEXT(Quote_Per_Adult__c), 2) & "," & MID(TEXT(Quote_Per_Adult__c), 3,3) & "," & RIGHT(TEXT(Quote_Per_Adult__c), 3),
9, LEFT(TEXT(Quote_Per_Adult__c), 3) & "," & MID(TEXT(Quote_Per_Adult__c), 4,3) & "," & RIGHT(TEXT(Quote_Per_Adult__c), 3),
10, LEFT(TEXT(Quote_Per_Adult__c), 1) & "," & MID(TEXT(Quote_Per_Adult__c), 2,3) & "," & MID(TEXT(Quote_Per_Adult__c), 5, 3) & "," & RIGHT(TEXT(Quote_Per_Adult__c), 3),
11, LEFT(TEXT(Quote_Per_Adult__c), 2) & "," & MID(TEXT(Quote_Per_Adult__c), 3,3) & "," & MID(TEXT(Quote_Per_Adult__c), 6, 3) & "," & RIGHT(TEXT(Quote_Per_Adult__c), 3),
12, LEFT(TEXT(Quote_Per_Adult__c), 3) & "," & MID(TEXT(Quote_Per_Adult__c), 4,3) & "," & MID(TEXT(Quote_Per_Adult__c), 7, 3) & "," & RIGHT(TEXT(Quote_Per_Adult__c), 3),
null) &"."& lpad(text((Quote_Per_Adult__c-floor(Quote_Per_Adult__c))*100),2,"0")
Nitin Saxena 17Nitin Saxena 17
Hi , I am trying to use your logic but getting syntax error

My formula field logic:

IF(ISPICKVAL(IN_Copay_Applies_for_Mail_Order__c, 'Applies'), 'Co-Pay:'+'Applies'+ ' | ' + '$' +
    IF(IN_Copay__c >= 1000000,
      TEXT(FLOOR(IN_Copay__c / 1000000)) & ",",
      "") &
    IF(
      IN_Copay__c >= 1000,
      RIGHT(TEXT(FLOOR(IN_Copay__c / 1000)), 3) & ",",
      "")
      & RIGHT(TEXT(FLOOR(IN_Copay__c)), 3) & "." &
    IF(MOD(IN_Copay__c , 1) * 100 "0" & TEXT(ROUND(MOD(IN_Copay__c , 1), 2) * 100),
      TEXT(MIN(ROUND(MOD(IN_Copay__c , 1), 2) * 100, 99))),

IF(ISPICKVAL(IN_Coinsurance_Applies_for_Mail_Order__c, 'Applies'), 'Co-Insurance:'+'Applies'+ ' | ' + IN_Coinsurance__c + ' %' ,

'Co-pay/Co-Insurance : N/A'))


Error : Syntax error. Missing ')'
 
Nitin Saxena 17Nitin Saxena 17
The value in Copay (currency) i am storing in a formula field and want decimal and comma in value displayed