You need to sign in to do that

Don't have an account?

WPCMS

# 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

SteveMo__c

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

WPCMS

I did that and it came out as

1000 and not $1,000.00.

New monthly rate going forward is 1000

SteveMo__couch!

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

WPCMS

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?

SteveMo__cDo you

reallyneed 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.WPCMS

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.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 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.

SteveMo__c

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

TinderJosh

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, ")", "")

oleksiyA little modifications to the formula from above:

Daniel Camp 15This 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 15Ok... 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 Azarudeenhere is the solution for this question http://salesforcemaniacs.blogspot.in/2016/02/convert-currency-into-words-in-salesforce.html ram4SFDCI 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 14I 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 jjvdevTim'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.3909373019751619E12Use 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 VangIf 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 CarrijoWorks like a charm, thanks! jbardetConfirming 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!

docbillNone 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 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?