Removing $ from strings

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!

1613658305911blob copy.pdf

Developer

Common Use Cases

Accepted answers

echuong1

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.

New Workflow1.yxmd

All comments

echuong1

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.

New Workflow1.yxmd

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.

currency to number and back.yxmd

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!

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!

Dollar sign removal formula.PNG

Dollar sign removal error_Alteryx.PNG

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703