1

I created input text field (money__c)value is 55995999. Where in this value, how to add commas using text formula field value like 5,59,95,999

Can anyone please guide?

  • 3
    Why don't you use Currency data type provided by Salesforce. This will make your job easier. Otherwise you need to parse that text which is a tedious job – Rohit Mourya Feb 15 '17 at 12:43
  • @Rohit Mourya i am already used that field in bulk of code . so i need to change comma separated value using text formula field. – Sfdcprgmr Feb 15 '17 at 12:47
  • What does that comma separated value stands for? Is it currency or just a number? – Rohit Mourya Feb 15 '17 at 12:53
  • @Rohit Mourya Number Only – Sfdcprgmr Feb 15 '17 at 12:57
  • Then use this formula in your formula field: VALUE(money__c ). This will convert text into number but make sure formula field is of Number type – Rohit Mourya Feb 15 '17 at 12:59
1

Use the following formula. It handles numbers up to 999,999,999.99 and rounding to 2 decimals, adjustable.

/* uncomment and adjust currency sign */
/* "$ " & */

/* uncomment next and last lines to denote negatives using the American
accounting style -1234 == (1,234.00),otherwise it's shown as -1,234.00 */ 
/* ! IF(Cash_L__c < 0, "(", "") & */ 

/* comment next line if uncommented line above */
IF(Cash_L__c < 0, "-", "") & 

SUBSTITUTE( 

/* multiplier and divider of 100 ('* 100' and '/ 100') indicate number of decimal
 places (2), adjust all if you are going to round to a different decimals */

/* millions */
IF(ABS(ROUND(Cash_L__c,2)) >= 1000000,
 TEXT(FLOOR(ROUND(Cash_L__c * 100, 0) / 1000000 / 100)) & ",", "") & 
/* thousands */
IF(ABS(ROUND(Cash_L__c,2)) >= 1000,
 RIGHT(TEXT(FLOOR(ROUND(Cash_L__c * 100, 0) / 1000 / 100)), 3) & ",", "") &
/* hundreds */
RIGHT(TEXT(FLOOR(ROUND(Cash_L__c * 100, 0) / 100)), 3) & 

/* '*100' and ROUND parameter of 2 indicate number of decimal places (2)*/
"." & RIGHT(TEXT(ROUND(Cash_L__c * 100, 0)), 2)

,"-","")
/* ! & IF(Cash_L__c < 0, ")", "") */

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.