Converting String to Number Results in Negative Numbers becoming zero

[jaypee1217]

Hi I'm brand new to Alteryx and am having issues converting a string column to values. Snip below of my columns and the resulting output.

- Transactions Amount column is the original string value

- Removed $ column is result of:  ReplaceChar([Transaction Amount],"$",""), but still makes this a string data

- Converted Amount column is result of a Tonumber function I found: tonumber(replacechar(trim(regex_replace([Transaction Amount],"^-0{1,}",'-'),"0"),"$,",""),1)

- Converted Removed $ column is me applying the Tonumber function to the Removed $ column thinking the $ character is  what was messing it up. 

Additionally when I use the select tool and change from tring to double, the negative amounts go to null as well

[gautiergodard · Accepted solution]

Hello @jaypee1217 

I believe the attached is what you are looking to do!

[DanFlint]

Hi @jaypee1217,

Try this formula:

IF StartsWith([Removed $],'(')
THEN ToNumber('-' + REGEX_Replace([Removed $],'\((\d+.*\d*)\)','$1'))
ELSE ToNumber([Removed $])
ENDIF

You could also nest the logic to remove the '$' sign into this formula if you wanted to keep it all in one.

Hope this helps!

[jaypee1217]

thank you! your simplified ToNumb function worked! I just need to try and next this together now. 

[jaypee1217]

Thanks Dan! gautiergodard's function worked when i loaded it. going to try and nest it together now.