Handling Thousand Separators and Decimal Commas in Alteryx Calculations

Hello everyone,

I am having issues with the data format in Alteryx. In my input, numbers are formatted like 642.200,19.

I need to perform calculations in Alteryx, so I have to convert the column, which appears as a string in the input, to double.

Is there a more efficient way than using replace formulas to switch the format to the American style, remove thousand separators, and then convert to double?

And does anyone know a quick method to insert thousand separators (as a period) into a number like, for example, 642200,19?

It should be done for an entire column in the correct format, where the column contains values of varying magnitudes (for example, those that require two thousand separators as well).

I appreciate any help you can offer—thank you!

Best regards,

Kim

Best Practices

Accepted answers

SPetrie

Formula ToNumber([number],0,1,",") will take the European format and convert it to American style.

Formula ToString([output],2,".",",") will take an American style number and convert it to European style.

All comments

aatalai

@kim09, you could try using the multi-feild tool, so that you could use the replace formula and change the data output in one tool

dreldrel

A formula tool would also be helpful if you're not an advanced user, then you can consider a regex tool or multi-field tool (mentioned by @aatalai ) after you understand how it actually works

jrlindem

Using REPLACE to swap out the comma's and period's is the right way to do that. Like @aatalai if you need to do this across many fields, using a Multi-Field tool is useful. To insert the comma's though... while you're in the workflow the specific string formatting is unnecessary; so I would recommend ignoring that need until your output. Then handle the output at the end. If you're going back into a database, the number formatting isn't compatible. If you're going into a table or excel, then let that get handled at that time.

Hope that helps, -Jay

SPetrie

Formula ToNumber([number],0,1,",") will take the European format and convert it to American style.

Formula ToString([output],2,".",",") will take an American style number and convert it to European style.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703