adding comma and bracket in Data

[kauser]

Hello, 

How can i add comma, bracket (only for negative data) and two decimal number into my output data. I tried multi Row formula tool but issue is these output data stored as text  

Current Data 

Product	Q1	Q2	Q3	Q4	Q5
A	45798	56458	-45896	-8793	-8950
B	-2589	-5896	4578	51489	-12508
C	-89635	4589	9856	-89345	39572

Expected Data  

Product	Q1	Q2	Q3	Q4	Q5
A	45,798.00	56,458.00	(45,896.00)	(8,793.00)	(8,950.00)
B	(2,589.00)	(5,896.00)	4,578.00	51,489.00	(12,508.00)
C	(89,635.00)	4,589.00	9,856.00	(89,345.00)	39,572.00

[cjaneczko]

If you add the commas and parenthesis you will not be able to do any calculations with this data in Excel after its exported. If you plan to do any mathematical functions after the export the best bet is to export the file to Excel and then perform the formatting in Excel. Save that file and on the next run of the workflow check the "Preserve Formatting" box in the output tool. If you add the commas and parenthesis in Alteryx its going to treat the field as a String not a Number. 

[kauser]

Yes that's why i am trying to format data at last point just before output tool. i want to format data with comma, decimal and bracket just before transfer to output tool. Also i didn't get your "Peserve Formatting" function. 

[Qiu]

@kauser 
Although we can choose the Thousand Seperator in the User settings, but it will not be visible in the result grid.
So I came up with the following by invovling some brutal force. 😂
and referred this thread.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Formula-Tool/m-p/759449#M18322...

[kauser]

@Qiu Thanks for your suggestion but if any cell have more than 6 digits then i am having output like this below- 

1. no comma after 6 digits (Second column)

2. comma after negative sign (Third column)

Output Data

A	8643,245,798	-,456,458
B	-89254,672,589	-,325,896
C	-4637,889,635	-,194,589

Input Data

Product	Q1	Q2
A	8643245798	-456458
B	-89254672589	-325896
C	-4637889635	-194589

[Raj · Accepted solution]

@kauser @Qiu something comparatively easy to understand 😅

if [_CurrentField_]<0 then Replace("("+ToString([_CurrentField_],2,1)+")","-","") else ToString([_CurrentField_],2,1) endif

hope this helps..

[Raj]

@kauser please mark done if this solves your problem.

[Raj]

@kauser 

Small change in formula which is edited in replay
if [_CurrentField_]<0 then Replace("("+ToString([_CurrentField_],2,1)+")","-","") else ToString([_CurrentField_],2,1) endif

[Qiu]

@Raj 
Thank you very much for letting know this feature.

😁