Removing $ from strings

[katieshore]

Hi, all. I am still pretty new to Alteryx and constantly learning. I am stuck trying to remove the "$" from my string records in a particular column. 

I am using the below formula (picture attached) in my formula tool, but it is turning my negative values into "0", which is not what I want: I would like a number ($908.68) to be -908.68.

From that point, would I use a "Select" tool to then convert the string into a number? I need two decimal points to appear (since this is currency) so what data type should I use to see two decimal points (even if it is a whole number like 40 - I would want to see 40.00). 

CORRECTION: My formula should say TrimLeft([Amount], "($"). My apologies!

Thanks!

[echuong1 · Accepted solution]

You can use replace statements to remove the parenthesis and dollar symbol. I used a conditional statement to add a negative if there is a parenthesis present.

I also used tonumber() to make the value a number. The precision has a scale of 2, for 2 decimal places.

[apathetichell]

Here is  a two part formula - the first takes a string, removes the "$" and converts it to a fixed decimal. Hypothetically this should create a whole number with two trailing zeros - but it doesn't on my experiments.

I've included a second formula which would take a fixed decimal and convert back to a currency string and add back the zeros for whole numbers.

Hope this helps.

[katieshore]

This works beautifully, thank you!

Would you have any idea why the Select tool would remove any zeroes after the number? For example, the formula would give me 50.00, but the Select tool output would only show 50. The same thing is happening for numbers like 40.80, which would come out of the Select tool as 40.8. 

I wonder if it has anything to do with the fact that I'm working with over 2 million rows of data, but I'm not sure... Thanks so much for your help!

[echuong1]

Ensure the datatype is set to a fixed decimal with a scale of 2. I'm guessing your datatype is set to something like float or double, and that is why it's removing the extra zeros.

[katieshore]

That's what I was thinking, but my data type is set to FixedDecimal 19.2 in the Formula tool and in the Select tool... Not really sure what's going on.

[apathetichell]

I'm seeing the same issue where the select tool compresses the decimals on fixed decimal.

[katieshore]

That's a good fix. I'm needing the values to be numeric for calculations, though, so I'm not sure that will work for my case. Thank you for your suggestions!

[Sir_Paul]

Hi all!  I've run into an issue:  I attempted to remove "$" from my financial data; however, not all the values transferred correctly (see attached).  Do I need to adjust the fixed decimal callibration in my formula tool?  Please advise.  Thank you!