Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Replicate Excels 'Comma Style' formatting of numbers

Alastair
7 - Meteor

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?

6 REPLIES 6
Aguisande
15 - Aurora
15 - Aurora

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
7 - Meteor

@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
15 - Aurora
15 - Aurora

@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
7 - Meteor

@Aguisande ok great. I will try that.

 

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

Aguisande
15 - Aurora
15 - Aurora

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

 

strings.PNG

Alastair
7 - Meteor

@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.

Labels