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

Alteryx designer Discussions

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

Convert a string with a negative number with the following pattern: - $3.33

Atom

I have string with a negative dollar amount that appears in the following format: - $3.33.

 

How can I change this to a negative number? I am having difficulty with the two solutions posted. They do not catch the negative amounts: 

 

Example 1

Formula Tool -  ToNumber(Replace(Replace(Replace(Replace([Data], '$', ''), ',' , ''), ')' , ''), '(' , '-'))

 

Example 2

Formula Tool - ToNumber(REGEX_Replace(REGEX_Replace([Data], "\$|,|\)", ""), "\(", "-"))

 

In both cases, the number returned is 0

Comet

You can use a replace formula to remove the dollar sign. Then, use a data cleansing tool to remove all whitespace in the field. Finally, use a select tool to set the data type to the number format you want. 

 

 

Negative Number Solution.PNG

Labels