Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a text field whereby I would like to replace the thousand delimeter from a comma to return a blank/space instead
Formula to convert text to return a comma as thousand delimeter:
=num([FTL Price],'#,###')
Changing the above to return a space:
=num([FTL Price],'# ###')
this returns
" 2070 ### "
does anyone know how I adapt my formula, so that it displays "2 070" instead?
I've changed my "Main" script tab, so that thousands are a space - but don't think this is the issue...?
SET ThousandSep=' ';
Any thoughts greatly appreciated...
Kind regards,
Rich
Hi,
Try this,
=Num([FTL Price],'# ##0','.',' ')
This works for me
=Num(2000000,'# ##0','.',' ')
displayed as 2 000 000
Hope this helps you.
Regards,
Jagan.
also tried:
num([FTL Price],' #,### ' , '.' , ' ' )
where '.' returns the decimal separator
' ' returns the thousand separator
although this only returns 2,070 and not the desired 2 070
I think we can't number format with space. You to replace comma's with space. For that use this code
=Replace(num([FTL Price],'#,##0','.',','),',',' ')
Hi Rich
You should award Jagans answer as a correct one! its an important motivator for people to answer posts. Good post though and it helped me solve my issue too 🙂
Best Regards
/Mats
Rich I have just actually worked on a dynamic switch for EUR and GBP formatting: -
I generated an Inline table to convert the entire document (below)
CurrencyIsland:
LOAD * INLINE [
CCY, Prefix, Format
'EUR', 'EURO', "'# ##0',',',' '"
'GBP', 'GBP', "'#,##0','.',','"
];
Incase you are reformatting the entire document then you can use =Only(Format)
Hi,
Try this,
=Num([FTL Price],'# ##0','.',' ')
Hope it helps
Hi,
Try this,
=Num([FTL Price],'# ##0','.',' ')
This works for me
=Num(2000000,'# ##0','.',' ')
displayed as 2 000 000
Hope this helps you.
Regards,
Jagan.
To allow me to reuse my templates I'm trying to use the DecimalSep and the ThousandSep variables (set on the Main tab) as the 3rd and 4th paramaters of the Num() function.
Seems that a space ( ) is not accepted as the ThousandSep however. Presumably it is being trimmed.
Jonathan