Ask Search:
Hamid PHamid P 

Creating a currency field to change $4,000 to 4,000$

Hi,

We have both English and French customers. The default currency field shows the amounts like this: CA$4,000. We use it when generating quotes for our English customers.

But when we want to generate quotes for French customers, the currency must be shown like this: CA4,000$

Is there any way that I can create a new currency field that takes the amount from the original currency field and instead of $4,000, shows is as to 4,000$ so that I can use this fields to generate quotes for our French customers?

 
Best Answer chosen by Hamid P
Tony TrinhTony Trinh
Hello Hamid,

Use this solution if the commas are important.
"CA" + CASE(LEN(TEXT(Currency__c)),
1, TEXT(Currency__c),
2, TEXT(Currency__c),
3, TEXT(Currency__c),
4, LEFT(TEXT(Currency__c), 1) + "," + RIGHT(TEXT(Currency__c), 3),
5, LEFT(TEXT(Currency__c), 2) + "," + RIGHT(TEXT(Currency__c), 3),
6, LEFT(TEXT(Currency__c), 3) + "," + RIGHT(TEXT(Currency__c), 3),
7, LEFT(TEXT(Currency__c), 1) + "," + MID(TEXT(Currency__c), 2,3) + "," + RIGHT(TEXT(Currency__c), 3),
8, LEFT(TEXT(Currency__c), 2) + "," + MID(TEXT(Currency__c), 3,3) + "," + RIGHT(TEXT(Currency__c), 3),
9, LEFT(TEXT(Currency__c), 3) + "," + MID(TEXT(Currency__c), 4,3) + "," + RIGHT(TEXT(Currency__c), 3),
10, LEFT(TEXT(Currency__c), 1) + "," + MID(TEXT(Currency__c), 2,3) + "," + MID(TEXT(Currency__c), 5, 3) + "," + RIGHT(TEXT(Currency__c), 3),
11, LEFT(TEXT(Currency__c), 2) + "," + MID(TEXT(Currency__c), 3,3) + "," + MID(TEXT(Currency__c), 6, 3) + "," + RIGHT(TEXT(Currency__c), 3),
12, LEFT(TEXT(Currency__c), 3) + "," + MID(TEXT(Currency__c), 4,3) + "," + MID(TEXT(Currency__c), 7, 3) + "," + RIGHT(TEXT(Currency__c), 3),
null) + "$"
I referenced this previous success thread and tweaked it a bit
https://success.salesforce.com/answers?id=90630000000gpInAAI

All Answers

Tony TrinhTony Trinh
Hello Hamid,

Is it important that the comma is in the number? If not, you can use:
"CA" + TEXT(Currency__c) + "$"

This will display as CA4000$ and NOT CA4,000$
Hamid PHamid P
Thanks a lot Tony!
 
Tony TrinhTony Trinh
Hello Hamid,

Use this solution if the commas are important.
"CA" + CASE(LEN(TEXT(Currency__c)),
1, TEXT(Currency__c),
2, TEXT(Currency__c),
3, TEXT(Currency__c),
4, LEFT(TEXT(Currency__c), 1) + "," + RIGHT(TEXT(Currency__c), 3),
5, LEFT(TEXT(Currency__c), 2) + "," + RIGHT(TEXT(Currency__c), 3),
6, LEFT(TEXT(Currency__c), 3) + "," + RIGHT(TEXT(Currency__c), 3),
7, LEFT(TEXT(Currency__c), 1) + "," + MID(TEXT(Currency__c), 2,3) + "," + RIGHT(TEXT(Currency__c), 3),
8, LEFT(TEXT(Currency__c), 2) + "," + MID(TEXT(Currency__c), 3,3) + "," + RIGHT(TEXT(Currency__c), 3),
9, LEFT(TEXT(Currency__c), 3) + "," + MID(TEXT(Currency__c), 4,3) + "," + RIGHT(TEXT(Currency__c), 3),
10, LEFT(TEXT(Currency__c), 1) + "," + MID(TEXT(Currency__c), 2,3) + "," + MID(TEXT(Currency__c), 5, 3) + "," + RIGHT(TEXT(Currency__c), 3),
11, LEFT(TEXT(Currency__c), 2) + "," + MID(TEXT(Currency__c), 3,3) + "," + MID(TEXT(Currency__c), 6, 3) + "," + RIGHT(TEXT(Currency__c), 3),
12, LEFT(TEXT(Currency__c), 3) + "," + MID(TEXT(Currency__c), 4,3) + "," + MID(TEXT(Currency__c), 7, 3) + "," + RIGHT(TEXT(Currency__c), 3),
null) + "$"
I referenced this previous success thread and tweaked it a bit
https://success.salesforce.com/answers?id=90630000000gpInAAI
This was selected as the best answer