community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Changing (100) to -100

Meteor

Hey guys,

 

So I am pulling a lot of account balances from excel and summarizing them by different groupings. The only problem is that my input files have negative balances in the for of (###.##) and these parentheses are breaking my sums. Is there a formula to change "(###.##)" to "- ###.##"?

Nebula
Nebula

Hi @all163 

 

You can handle this by using a Formula tool with the following 

 

Replace(Replace([fieldname],"(","-"),")","")

 

This will replace the open bracket with "-" and the closing bracket with "" (blank string).  Your field will still be a string, so consider using a Select tool to change it's type to double

 

Dan

Highlighted
Alteryx Certified Partner

Hey @all163 

 

Try this in the formula tool:

 

If REGEX_Match(Number, "\(.*\)")Then

ToNumber(ReplaceChar(Number, "()", ""))*-1

Else [Number]

EndIf

Pulsar
Pulsar

Hi @all163 

You can take either of the two approaches promoted by @LordNeilLord and @danilang (both are fantastic) and put into a multi-field formula tool. That will let you change the data type to a numeric data type so that you can use it in a sum.'

 

Or, in the formula tool, you can create a new field based off of the old one, and make it numeric.

 

Cheers!

Esther

Labels