Number Formatting

I have a Text file that I have imported via FLAT ASCII file.

The text file has invoice prices that are listed in the following format (below) because they are European invoices.

10000,00

I need the output to form as

10,000.00

I have used a formula to replace ',' with '.' however I cannot add in the thousand delimiter. I have tried using ToString([original_value], 2, 1) formula but have had no success. Any ideas or help would be appreciated.

Developer

Accepted answers

DavidP

For tostring to work, the number has to be in a number format first. Try something like this:

tostring with thousand separator.yxmd

All comments

DavidP

For tostring to work, the number has to be in a number format first. Try something like this:

tostring with thousand separator.yxmd

krobe158

Amazing, thanks. Happy Monday!

JokeFun

hi @DavidP what's the purpose of "Replace([Field1], ',', '.')"

I tried tostring([Field1],2,3) which has the same result. But I believe you have a reason to add the replace function here.

BenMoss

Hi [at-mention author],

As a leader in the Alteryx Community, I have the ability to identify & mark accepted solutions on behalf of community members - and recently did so on this thread. If you have any questions or concerns with the solution(s) I selected please let me know by replying to this post.

As the original author, you also have the ability to mark replies as solutions! Going forward, I’d encourage you to identify the solution or solutions that helped you solve your problem, as it's a big help to other community members. Learn more about Accepted Solutions here.

Thank you!

Machteld

Dear Ben,

Using Alteryx I have a simular situation.

The programm producing my data has an output in a text file.

The amounts are in European format, and therefore are not recognised as numbers in Alteryx.

As mentioned above I have tried based on the solution mentioned above, but I keep on losing information.

Could you help me with a solution that will keep my data intact?

This is an example of what I've tried:

The data going in is a string called salaris and it holds 2876,36 which is 2,876 euro's and 36 cents.

After using tonumber with replace and multiplieing by 1,000 I only have the euro amount and I have lost the cents.

This is the exact formula I used:

tonumber(Replace([salaris],',','.'))*1000

Do you know a way to transform the stingdata 2876,36 to 2,876.36 without losing the last two digits?

Kind regards,

Machteld

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703