Format columns

JDong

Hi Team,

I have the 2 columns in my data.

Price column is to be formatted with a thousand separator and on 2 decimal for all values. Example : 6.3211,00.

Average column is to be formatted as 2 decimals Ex : 0,06

The final field is needed as a string. 

Price	Average
63221	0
7805	0
38245	0
33562	0
30440	0
78052	1
24976	0
1561	0
12488	0
24196	0
7805	0
0	0
122541	1
56197	0
243521	2
485482	5
170152	1
9366	0
9366	0
0	0
27318	0
5463	0
0	0
0	0
266152	2
94441	0
117074	0
195128	2
61660	0
34342	0
51513	0
17951,96	0,23
13268,84	0,17
5463,64	0,07
21074,04	0,27
41367,56	0,53
28098,72	0,36
5463,64	0,07
11707,8	0,15
3902,6	0,05
35903,92	0,46
24976,64	0,32
37464,96	0,48
8360150

Please advise. Also to note the column already has different values but need to format and make them consistent. Thanks

AdamR_AYX

OK sorry just checked and this was a new feature in 2020.4

https://help.alteryx.com/release-notes/designer/designer-20204-release-notes

I can do it in one formula in previous versions. It's just an ugly formula:

Replace(Replace(Replace(ToString(ToNumber(Replace([_CurrentField_], ",", ".")), 2, 1),
".", "¬"),
",", "."),
"¬", ",")

To walk through it (from the inside out)

We replace , with . and then convert to a number

Then convert the number back to a string with 2 decimal places and thousand separator. But , for the thousand separator and . for decimal separator.

Then replace the . with a ¬ (temporary placeholder for this character)

Replace the , with a .

Finally replace the ¬ with a ,

FormatNumbers.yxmd