Forum Discussion
Localization numeric formatting (periods and commas)
can anyone help me to format million sperator to Crore, lakh seperator like 123,456,789.00 to 12,34,56,789.00 ?
3 Replies
Hi pavitrajindal,
Take a look at below formula if it suitable for your requirment:
Format =
var b= INT(MOD([Amount],10000000)/100000)
var c= INT(MOD([Amount],100000)/1000)
var d= INT(MOD([Amount],1000))
return
IF(LEN([Amount])>=6,b&","&REPT("0",2-LEN(c))&c&","&REPT("0",3-LEN(d))&d,if(LEN([Amount])>=4,c&","&REPT("0",3-LEN(d))&d,d))&".00"Notice, Above formula will convert the type to text and it is hard to convert the formatted text to numeric type.
Regards,
Xiaoxin Sheng
i need number to be in Numeric format not in string format, is there anything like we do in Excel using custome format ?
Also my number is big it is in billions
Hi pavitrajindal,
I haven't found a way to format numeric value and keep it's type.
In addition, you can also use format function to format the display text(it also change the columns type)Format =
IF (
[Amount] >= 1000000,
FORMAT ( [Amount], "#,#0,##0" ),
IF (
[Amount] >= 1000,
FORMAT ( [Amount], "#,##0" ),
FORMAT ( [Amount], "General Number" )
)
)Regards,
Xiaoxin Sheng
