Good Day Everyone I have this requirement where the amount field needs to be formatted against various formats, here is a sample. Thanks for your help in advance. Amount To be converted as 830000830,000.0015.000.00015,000.0010.000.00010,000.00250250.0023.000.00023,000.00130.000.000130,000,00250.040.000250,040.00213.000.000213.000.0025.000.00025,000.00

@AbhijeetChib Offering a non-regex solution: * Check if the does not contain a period (if so, remove commas only). * Check if number does not contain a comma (if so, remove periods only). * Check if the position of the first comma is less than the position of the first period* If it is, remove the commas * If it isn't, remove the periods and replace the comma with period * Make this cleaned file numeric * Use the tostring function to get the desired format New Workflow1.yxmd

Formatting the amount field

AbhijeetChib

Good Day Everyone

I have this requirement where the amount field needs to be formatted against various formats, here is a sample.

Thanks for your help in advance.

Amount	To be converted as

830000	830,000.00
15.000.000	15,000.00
10.000.000	10,000.00
250	250.00
23.000.000	23,000.00
130.000.000	130,000,00
250.040.000	250,040.00
213.000.000	213.000.00
25.000.000	25,000.00

Data Investigation

Accepted answers

All comments

AbhijeetChib

Another set of format added to the above one

1234567	1.234.567.00
1.234.567,00	1,234,567.00
12.345,67	12,345.67

Thanks again @binu_acs

Check this out, why am I getting incorrect values.

Thanks again @binu_acs

One thing gets fixed and breaks the other. I am attaching the entire sample data set. I have added I to the incorrect values, thanks again for your help. Ideally all thousands should be separated by "," and decimal by "."

Thanks a lot again

ForAlteryx.xlsx

binu_acs

@AbhijeetChib

AmountFormatting1.yxmd

Luke_C

@AbhijeetChib Offering a non-regex solution:

Check if the does not contain a period (if so, remove commas only).
Check if number does not contain a comma (if so, remove periods only).
Check if the position of the first comma is less than the position of the first period
1. If it is, remove the commas
2. If it isn't, remove the periods and replace the comma with period
Make this cleaned file numeric
Use the tostring function to get the desired format

New Workflow1.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335