+ Start a Discussion
WPCMSWPCMS 

How to Have a Currency Field in a Formula Text Field

I have an IF statement that needs to read

 

"New monthly rate going forward is $1,000.00"

 

My formula is

 

IF(ISBLANK( Custom_Description__c ),
"" ,
"New monthly rate going forward is" &""& Opportunity__r.Current_Monthly_Revenue__c)

 

but the Opportunity__r field is a currency field and the formula Error says "Incorrect parameter for function &(). Expected Text, received Number

 

Thank you in advance

Steve MolisSteve Molis

You need to convert it from Currency to Text using a TEXT function in your formula.

 

TEXT(Opportunity__r.Current_Monthly_Revenue__c)

 

 

WPCMSWPCMS

I did that and it came out as

1000 and not $1,000.00.

 

New monthly rate going forward is 1000

Steve MolisSteve Molis

ouch! 

okay, if you need your text field to appear exactly in Currency Format then I'm afraid you're gonna have to parse the whole thing out using LEFT, RIGHT, MID functions and then manually insert the "$"",""." as needed

https://na3.salesforce.com/help/doc/en/customize_functions.htm

WPCMSWPCMS

Wow, that is a headache. There is no way to turn a date, currency, or picklist field into separate text fields and then concatenate the new text fields into my custom text field?

 

Any ideas?

Steve MolisSteve Molis

Do you really need to have the comma and decimal?  I mean is you just slap a "$" on the front of it  you should be all set.  Otherwise the only other way to do it is to pasre out each chunk and then reconstruct it by inserting the: $, comma, decimal.

WPCMSWPCMS

I did that and I am not happy with the out put as most of the currency fields are more than 3 digits. But I also am trying to concatenate other field types together in one line that are dates, picklists, etc.

 

If I can't just do that this project is going to take much longer!

Jeremy.NottinghJeremy.Nottingh

This would not be that hard to do with an Apex Trigger, if you can do that in your organization. You could also set up a standard class to format currency the way you like it, and use it anytime you need to do the same thing.

 

Jeremy

Tim MauroTim Mauro

A little Google searching turned up this gem:

 

http://techblog.appirio.com/2010/02/displaying-currencies-in-sfdc-formula.html

 

To cut to the chase, here's his solution - brace yourself...

 

IF( 
  someCurrencyField__c >= 1000000, 
  TEXT(FLOOR(someCurrencyField__c / 1000000)) & ",", 
  "") & 
IF( 
  someCurrencyField__c >= 1000, 
  RIGHT(TEXT(FLOOR(someCurrencyField__c / 1000)), 3) & ",", 
  "") & 
RIGHT(TEXT(FLOOR(someCurrencyField__c)), 3) & "." & 
IF( 
  MOD(someCurrencyField__c , 1) * 100 < 10, 
  "0" & TEXT(ROUND(MOD(someCurrencyField__c , 1), 2) * 100), 
  TEXT(MIN(ROUND(MOD(someCurrencyField__c , 1), 2) * 100, 99)) 
)

 

Append a "$" if you like.  Worked like a charm for me.

 

Steve MolisSteve Molis

You need to convert the Currency Field to a Text values using the TEXT function like I posted above

 

IF(ISBLANK( Custom_Description__c ),
NULL,
"New monthly rate going forward is" &" $"& 
TEXT(Opportunity__r.Current_Monthly_Revenue__c))

 

TinderJoshTinderJosh

Here's another way adapted from the one above to handle negative numbers:

 

This code for a Salesforce formula will take a number and return a formatted string representation of the number in American style Currency. It will also handle negatives and denote them using the American accounting style -1234 == (1,234.00)

 

To use, simply replace "Amount" with the field in question.

 

IF(Amount < 0, "(", "") & "$" &
IF(ABS(Amount) >= 1000000, TEXT(FLOOR(ABS(Amount) / 1000000)) & ",", "") &
IF(ABS(Amount) >= 1000, RIGHT(TEXT(FLOOR(ABS(Amount) / 1000)), 3) & ",", "") &
RIGHT(TEXT(FLOOR(ABS(Amount))), 3) & "." &
IF(MOD(ABS(Amount) , 1) * 100 < 10, "0" & TEXT(ROUND(MOD(ABS(Amount) , 1), 2) * 100), TEXT(MIN(ROUND(MOD(ABS(Amount) , 1), 2) * 100, 99))) &
IF(Amount < 0, ")", "")

oleksiyoleksiy
A little modifications to the formula from above:
/* uncomment next and last 2 lines to handle negatives and denote them in accounting style -1234 == (1,234.00) */
/* SUBSTITUTE(
IF(Amount < 0, "(", "") & */
/* uncomment and adjust currency sign */
/* "$" & */
IF(ABS(Amount) >= 1000000, TEXT(FLOOR(Amount / 1000000)) & ",", "")
& IF(ABS(Amount) >= 1000, RIGHT(TEXT(FLOOR(Amount / 1000)), 3) & ",", "")
& RIGHT(TEXT(FLOOR(Amount)), 3)
/* multiplier x100 and 2 indicate number of decimal places */
& "." & RIGHT(TEXT(ROUND(Amount * 100, 0)), 2)
/* & IF(Amount < 0, ")", "")
,"-","") */

 
Daniel Camp 15Daniel Camp 15

This code has worked great for me... Thanks!
But I need the Text (currency) to be rounded to the nearest dollar, not two decimal places.  How would the code be changed to accomidate for that? Is it just a matter of changing the 2 to a 0 at the end of line 10?
Daniel Camp 15Daniel Camp 15
Ok... I did some trial and error trying to get rid of the two decimal places and it looks like it was best for me to just remove lines 09 & 10... That got rid of the two decimal places.  I don't have negative numbers for this field so I also removed lines 11 & 12... everything looks good.
ram4SFDCram4SFDC
I think the best way to solution this is to use a formula field of type number allowing salesforce to auto format the number field based on the user's locale. This also helps format the currency field for any length.  
Michelle Lewis 14Michelle Lewis 14
I know this is a little old, but for anyone else who comes across this issue- Use the Value function- converts the  text string back to a number
jjvdevjjvdev
Tim's comment works...

To cut to the chase, here's his solution - brace yourself...
 
IF( 
  someCurrencyField__c >= 1000000, 
  TEXT(FLOOR(someCurrencyField__c / 1000000)) & ",", 
  "") & 
IF( 
  someCurrencyField__c >= 1000, 
  RIGHT(TEXT(FLOOR(someCurrencyField__c / 1000)), 3) & ",", 
  "") & 
RIGHT(TEXT(FLOOR(someCurrencyField__c)), 3) & "." & 
IF( 
  MOD(someCurrencyField__c , 1) * 100 < 10, 
  "0" & TEXT(ROUND(MOD(someCurrencyField__c , 1), 2) * 100), 
  TEXT(MIN(ROUND(MOD(someCurrencyField__c , 1), 2) * 100, 99)) 
)
dannapareddy1.3909373019751619E12dannapareddy1.3909373019751619E12
Use this Formula. Provided there are no Decimal places. 


if( LEN(Text(Loan_Amount__c))>15,
    ("$ "& Left(Text(Loan_Amount__c),LEN(Text(Loan_Amount__c))-15)&","&
     Left(Right(Text(Loan_Amount__c),15),3)&","&
    Left(Right(Text(Loan_Amount__c),12),3)&","&
   Left(Right(Text(Loan_Amount__c),9),3)&","&
   Left(Right(Text(Loan_Amount__c),6),3)&","&
   Left(Right(Text(Loan_Amount__c),3),3)&".00"

   )
,
(
 if( LEN(Text(Loan_Amount__c))>12,          
 ("$ "& Left(Text(Loan_Amount__c),LEN(Text(Loan_Amount__c))-12)&","&
  Left(Right(Text(Loan_Amount__c),12),3)&","&
   Left(Right(Text(Loan_Amount__c),9),3)&","&
   Left(Right(Text(Loan_Amount__c),6),3)&","&
   Left(Right(Text(Loan_Amount__c),3),3)&".00"

),
(
 if( LEN(Text(Loan_Amount__c))>9,          
 ("$ "& Left(Text(Loan_Amount__c),LEN(Text(Loan_Amount__c))-9)&","&
    Left(Right(Text(Loan_Amount__c),9),3)&","&
   Left(Right(Text(Loan_Amount__c),6),3)&","&
   Left(Right(Text(Loan_Amount__c),3),3)&".00"

),
(
 if( LEN(Text(Loan_Amount__c))>6,          
 ("$ "& Left(Text(Loan_Amount__c),LEN(Text(Loan_Amount__c))-6)&","&
   Left(Right(Text(Loan_Amount__c),6),3)&","&
   Left(Right(Text(Loan_Amount__c),3),3)&".00"

),
(
 if( LEN(Text(Loan_Amount__c))>3,          
 ("$ "& Left(Text(Loan_Amount__c),LEN(Text(Loan_Amount__c))-3)&","&
 
   Left(Right(Text(Loan_Amount__c),3),3)&".00"

),
(
"$ " & Text(Loan_Amount__c) &".00"
))))))))))
Bee VangBee Vang
If you have a field out there that determines if its GBP or US, you can do this. 

CASE(CurrencyIsoCode ,'GBP','£','$') &( 
IF(Amount < 0, "-", "") & 
IF(ABS(Amount) >= 1000000, TEXT(FLOOR(ABS(Amount) / 1000000)) & ",", "") & 
IF(ABS(Amount) >= 1000, RIGHT(TEXT(FLOOR(ABS(Amount) / 1000)), 3) & ",", "") & 
RIGHT(TEXT(FLOOR(ABS(Amount))), 3) & "." & 
IF(MOD(ABS(Amount) , 1) * 100 < 10, "0" & TEXT(ROUND(MOD(ABS(Amount) , 1), 2) * 100), TEXT(MIN(ROUND(MOD(ABS(Amount) , 1), 2) * 100, 99))))