Replicate Excels 'Comma Style' formatting of numbers

[Alastair]

Good day, I am trying to replicate Excels number formatting 'Comma Style' in Alteryx when outputting reports, i.e. format with a thousands separator (a space not a comma). Also, zero should appear as a dash, i.e. - instead of 0.

Any suggestions would be appreciated?

[Aguisande · Accepted solution]

Hi @Alastair

Using the optional parameters in the ToString function may help you.

Like:

ToString([number], 2, 1) 

For thge Zero, a simple formula will do:

IF [number]=0

  THEN "-"

  ELSE [number]

ENDIF

Hope this helps

Best,

_AG_

[Alastair]

@Aguisande thanks for the response! Much appreciated. 

The simple formula for replacing the zero works perfectly.

For the number formatting I used the following formula:  

ToString([Number], 0,1)  

It appears that when the number is longer than 6 characters, I get a conversion error: Number was truncated, i.e. 1,373,235 which outputs to 1,373,23. Any idea how to fix this? 

Oh and one more thing, if don't want the commas as separators but rather spaces, how would you go about that, i.e. 1 373 235 as an ouput.

[Aguisande · Accepted solution]

@Alastair

Maybe using the ReplaceChar function ( ReplaceChar(String, character to replace, character to replace with)) may help you. In this case it'll be ReplaceChar(String, ","," ")

[Alastair]

@Aguisande ok great. I will try that.

Any idea about the conversion errors, i.e. the truncating of long numbers?

[Aguisande · Accepted solution]

Please, check the length of the String being generated in the formula

[Alastair]

@Aguisande brilliant! I changes the size from the the default 8 to 12 and no further conversion errors.

The replace formula also worked. Thanks again.