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
Solved! Go to Solution.
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!
thank you! your simplified ToNumb function worked! I just need to try and next this together now.
Thanks Dan! gautiergodard's function worked when i loaded it. going to try and nest it together now.