Amount formatting

[AbhijeetChib]

Hello ! 

I have amount fields in the below format that need to be converted as 

1234567	1,234,567
1.234.567	1,234,567
1.234.567,89	1,234,567.89
12.345,67	12,345.67
123.345,00	123,456.00

[binu_acs]

@AbhijeetChib Can you check the user settings and check the thousand separator configured properly ?

[AbhijeetChib]

Hello @binu_acs 

Thanks for your response - The thousand separator is fine. 

Sometimes in the input files i get Amount in both formats 

123456789 as well as 1,234,567.89 and 1.234.567,89\

Need to ensure they all are separated by , in thousands and .in decimal

[binu_acs]

@AbhijeetChib 

[AbhijeetChib]

Thanks a lot @binu_acs 

Although it did solve a bit of the issue but it messed up others, here is the data

Thanks for your help again

[binu_acs]

@AbhijeetChib 

[AbhijeetChib · Accepted solution]

Thank you that works but i cant understand, would you be kind enough to break it down. 

For example what is the first condition that you are checking 

Sorry if i sound stupid. 

[binu_acs]

@AbhijeetChib the amount ending with decimal doesn’t need any update , the first condition checks whether the amount ending with decimal ( the RIGHT() will take the last 3 character then the LEFT () function check the first chat from left is a decimal or not) , if not ending with decimal then apply the formatting

[AbhijeetChib]

Thank you @binu_acs 

In the above sample the below formats are not being arranged properly

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

[binu_acs · Accepted solution]

@AbhijeetChib I replied to your latest post