Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!

Changing (100) to -100

Highlighted
7 - 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 "- ###.##"?

Highlighted
17 - Castor
17 - Castor

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

Highlighted
14 - Magnetar
14 - Magnetar

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