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?

5
  • 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 Feb 15, 2017 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, 2017 at 12:47
  • What does that comma separated value stands for? Is it currency or just a number? Feb 15, 2017 at 12:53
  • @Rohit Mourya Number Only
    – Sfdcprgmr
    Feb 15, 2017 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 Feb 15, 2017 at 12:59

1 Answer 1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .