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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

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

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 :) 

 

 

Highlighted

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

Quasar

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