0

I am trying to convert number to formatted string using a formula field. Conversion is from USD (which is the default org currency) to JPY. I didn't use the standard multi-currency function offered by Salesforce as it has limitations when it comes to custom objects and I don't want to have a problem with it on my future implementations.

Result of first testing is correct, given the ff. values for each field:

Correct:

USD_to_JPY_Rate__c (number field)
107.0000

Currency_Field__c (currency field)
$2,000.00

Converted Currency_Field__c (USD-JPY)
¥214,000.00

On my second testing, I changed Currency_Field__c value to $25,000.00, but I got ff. result:

Wrong:

Currency_Field__c (currency field)
$25,000.00

Converted Currency_Field__c (USD-JPY)
¥2,¥675,000.00

How can I correct this? Below is the formula:

Converted Currency_Field__c (USD-JPY)

IF( NOT( ISBLANK( USD_to_JPY_Rate__c ) ) ,
IF(( Currency_Field__c * USD_to_JPY_Rate__c ) >= 1000000,
'¥' & TEXT(FLOOR(( Currency_Field * USD_to_JPY_Rate__c ) / 1000000)) & ",",
"") &
IF(( Currency_Field * USD_to_JPY_Rate__c ) >= 1000,
'¥' & RIGHT(TEXT(FLOOR(( Currency_Field * USD_to_JPY_Rate__c ) / 1000)), 3) & ",",
"") &
RIGHT(TEXT(FLOOR(( Currency_Field * USD_to_JPY_Rate__c ))), 3) & "." &
IF(MOD(( Currency_Field * USD_to_JPY_Rate__c ) , 1) * 100 < 10,
"0" & TEXT(ROUND(MOD(( Currency_Field * USD_to_JPY_Rate__c ) , 1), 2) * 100),
TEXT(MIN(ROUND(MOD(( Currency_Field * USD_to_JPY_Rate__c ) , 1), 2) * 100, 99))
), '¥0.00')
2

You need to place the currency symbol only before the first IF block in the TRUE part.

IF(
    NOT ISBLANK( USD_to_JPY_Rate__c ),
    '¥' & IF(
        Currency_Field__c * USD_to_JPY_Rate__c >= 1000000,
        TEXT(FLOOR(Currency_Field * USD_to_JPY_Rate__c / 1000000)) & ",", 
        ""
    ) &
    IF(
        Currency_Field * USD_to_JPY_Rate__c >= 1000,
        RIGHT(TEXT(FLOOR(Currency_Field * USD_to_JPY_Rate__c / 1000)), 3) & ",",
        ""
    ) &
    RIGHT(TEXT(FLOOR(Currency_Field * USD_to_JPY_Rate__c )), 3) & "." &
    IF(
        MOD(Currency_Field * USD_to_JPY_Rate__c, 1) * 100 < 10,
        "0" & TEXT(ROUND(MOD(Currency_Field * USD_to_JPY_Rate__c , 1), 2) * 100),
        TEXT(MIN(ROUND(MOD(Currency_Field * USD_to_JPY_Rate__c , 1), 2) * 100, 99))
    ), 
    '¥0.00'
)
| improve this answer | |
  • Oh, I see it now. Modified the formula and it is working now. Thanks for the immediate response and help! – Ertyq Jun 12 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.