Negative sign should be replaced with ( in the double column

[Pravallika20]

Hi All,

Is there anyway we can replace the negative sign with '(' in the double data type

[apathetichell]

What you are asking is not possible. - is allowable in numbers - "(" and ")" are string components.

to fix this you'll have to convert from double to string...

You can do this by creating a new field and doing regex_replace(tostring([Field1]),"-([\d\.,]+)","($1)")

[Pravallika20]

Hi 

I tried using a table tool and added prefix  as ( and suffix as ) in the same double column. For example my source is -23456 my target is  (23,456) I can do this using the table tool. But after adding the prefix and suffix I am getting this as -(23,456) in the same double column. Is there any way I can hide this '-' sign?

[mceleavey]

Hi @Pravallika20 ,

As @apathetichell mentioned, this is a string function so you will need to handle it manually. 

First you will need to change the field to a string:

Then you can amend this in a formula tool:

if Contains([Number], "-") then replace("("+[Number]+")","-","") else [Number] endif

Hope this helps,

M.

[apathetichell]

Here's your work around - but note - this is kind of self-defeating if your goal is to have a negative number rendered in excel - however this does present as you want.

1) formula tool

take the abs() value  of your number

2) create a bool test variable if the two variables are equal

3) drop the original field in your table tool and include only the abs field...

4) create a column rule as pictured.

[Pravallika20]

Hi @apathetichell ,

Thank you for the solution but if we add the abs function, then the entire cell will be converted as positive number. But My question is its functionality should be the negative number. But it should be inside the brackets. Ex: if we are having -890 it should be popped as (890) in the excel it should be -890 not 890. I need the  Accounting Format to be precise.

[atcodedog05]

Hi @Pravallika20 

(890) abs is not supported output format in alteryx. Your requirement may not be possible 😅 

[apathetichell]

That was what I said. I said it just looks like (890) but the value will be 890 not -890... having no idea what you are working on - this would be fine for a .pdf render.  I believe you should go back to the string solution or write an excel macro or something... Perhaps you'd like you use a python script in excel?

Note - I do not believe that Python or R supports () as a recognized format for negative numbers.