num() - returning a blank/space as thousand delimiter

[#1]

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

[#2]

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

[jagannalla]

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','.',','),',',' ')

[#4]

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

[#5]

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)

[MayilVahanan]

Hi,

     Try this,

     =Num([FTL Price],'# ##0','.',' ')

     Hope it helps

[jagan · Accepted solution]

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.

[Anonymous]

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