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

Alteryx designer Discussions

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

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More

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

Aurora

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

Alteryx Certified Partner

Hey @all163 

 

Try this in the formula tool:

 

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

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

Else [Number]

EndIf

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