function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ 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 :-/Steve :-/

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 :-/Steve :-/

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 :-/Steve :-/

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 :-/Steve :-/

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.
Mohammed AzarudeenMohammed Azarudeen
here is the solution for this question http://salesforcemaniacs.blogspot.in/2016/02/convert-currency-into-words-in-salesforce.html
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))))
 
Karlos CarrijoKarlos Carrijo
Works like a charm, thanks!
jbardetjbardet
Confirming this worked great for me! Leanest code I could find for my scenario, similar to Daniel Camp's... i needed rounded to nearest dollar and do not have negative values:

IF(ABS(standardprice__c) >= 1000000, TEXT(FLOOR(standardprice__c / 1000000)) & ",", "")
& IF(ABS(standardprice__c) >= 1000, RIGHT(TEXT(FLOOR(standardprice__c / 1000)), 3) & ",", "")
& RIGHT(TEXT(FLOOR(standardprice__c)), 3)

I'm using this in a workflow field update into a long text field, with 5 different prices displayed, so I needed to find the leanest solution, otherwise I feared butting up against character count!
docbilldocbill

None of these solutions really work.  Some locales use commas to separate thousands, some as a decimal.  Some locales don't separate by groupings of 3.  ETC.  There really probably so no way to write a formula field that would not exceed salesforce limits that handles all locales correctly.   Worse, you cannot even access $User.DefaultCurrencyIsoCode, so there would be no way to show currency conversion if needed.

The best solutions are based around using triggers, or components, javascript, or other coding options.
Jennifer Le 28Jennifer Le 28
@jbardet your formula works for me, but I need the number to go to billions and this only works for millions. How do I edit the formula to show billions?
Mas StarMas Star
This tool is something that will be useful for solving your issues with text https://backlinkvalidator.com/tools/text_manipulation_tools.php
Brandon Johnson 15Brandon Johnson 15
I added a rounding function to two decimal places around each valueto the function from TinderJosh. This was necessary because some of our values were sub penny ammounts. The function without the round floors everything so you end up rounding down (i.e. 1299.999 becomes 1299.99 instead  of 1300.00). I also wanted negative sign instead of parenthesis. But overall, kudos to TinderJosh. That did the trick for us, nice formula!