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

Alteryx designer Discussions

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

Formula to remove comma and replace parentheses in negative numbers with "-"

Highlighted
Meteor

Hi,

I have data coming in as a V_WString that I need to convert to a number to do a running total later in the workflow. When I just switch the data type directly to a double, numbers in () become null and numbers with a comma are truncated. Can anyone help me with a formula to use? I've attached my failed attempts.

 

Thanks!

Hannah

 

 String to Number.JPG

Alteryx Partner

You were very close !!!  You had the right thinking, just missing a bit of syntax.  I used:

 

ToNumber(Replace(Replace(Replace([Original Number],",",""),")",""),"(",'-'))

 

and it worked in all rows :) 

 

 

I think:

ToNumber(Replace(REGEX_Replace([Original Number],"\((.*)\)",'-$1'),",",""))

should work

 

The Regex Replace sort out the brackets.

The next replace deals with the ,

Finally converts to a number

Pulsar
Pulsar

I came up with the same solution as @jdunkerley79, using the Replace and Regex_replace

 

What's great about this approach is it will work to also remove all numbers in parentheses that also have a comma.

 

Cheers!

Esther

Labels