Currency converter

[Plewis1]

Hello all, I have a full column with a currency but the numbers are long. I am trying to shorten the value up to a normal dollar currency and add a dollar sign symbol. I also want to do this to multiple columns at once. I tried using this formula '$'+Tostring([FIELD],2,1) and I keep getting the error "The formula resulted in a string but the field is numeric". It says to use Tonumber(...) but that's giving me an error as well. Any help is appreciated. Thank you

[RolandSchubert · Accepted solution]

Hi @Plewis1 ,

the formula results in a string, but the field is numeric, so it will not work within a Formula tool.

Try using the Multi-Field Formula tool. You can change the field type to "String" in the tool and do the conversion for all fields
Configuration could be like below:

Does this help?

Best regards

Roland

[TonyA]

Roland answered while I was putting together an example. I agree the Multi-Field Formula tool is the best way to go.

[danilang]

Hi @Plewis1 

The underlying cause of all this is that, unlike in Excel, in Alteryx you can't apply formatting information to a column independently of the column type.  In Excel, formatting details are stored apart from the actual data, so you can take a column of numbers and apply the Currency format without changing the numbers themselves.  In Alteryx, any change you try to make the data is applied to the data itself so it has to match the underlying data type.  Adding a string character like "$" to a number gives you a string result since numeric types can't hold string information, resulting in your "The formula resulted in a string but the field is numeric".  When you followed the advice and added ToNumber() you got the second error message, since ToNumber() doesn't recognize the "$"  as a valid part of a number.

Like both @TonyA and @RolandSchubert recommended, you need to store the result in a new string column or convert the existing field to a string.  If you convert the existing field to a string, however, you lose the ability to apply any numeric operations on it.  Multiplying a "$50.00" by 2 will throw an error.  The kind of conversions that you're looking to make are normally applied at the end of a workflow before the output step.  If you want to perform further mathematical operations in Alteryx, leave the numbers as is.

Dan 

[TonyA]

That's a very important point, @danilang. Thanks for catching it.

[Plewis1]

Thank you all for your help. That worked.