How do I correctly convert recurring decimals properly?

[HW1]

I have a column with string and numerical values which includes decimals too.

I want to maintain the integrity of these columns while converting the recurring decimal numbers to proper numbers like:

1068.06999999999
999.000999999999
999.001999999999
999.005999999999

Should be

1068.07
999.001
999.002
999.006

I am trying regex identify and convert however I am not getting the right output

I dont know how to correctly do the same

Please find example workflow attached.

[grazitti_sapna]

Hi @HW1, the formula you wrote in the workflow is correct you just need to use round function in order to achieve the desired result.

Here is the formula:

IF REGEX_CountMatches([Customer Number], '^(?!\d+(?:\.\d+)?$).*$') = 0 then Round(ToNumber([Customer Number]),0.01) else [Customer Number] endif

I hope this helps!

Thanks!

[binuacs]

@HW1 Another way of of doing this with the below formula

IIF(Contains([Customer Number], 'E'),[Customer Number],toString(toNumber([Customer Number]),2))

[flying008]

Hi, @HW1 

maybe you need the dynamic solution.

Input		Output
1009.00699999999		1009.007
1068.02199999999		1068.022
1068.02399999999		1068.024
1068.04099999999		1068.041
1068.06999999999		1068.07
999.000999999999		999.001
999.001999999999		999.002
999.005999999999		999.006
0		0
0E003		0E003
0E004		0E004
0E006		0E006
0E007		0E007
0E009		0E009
0E00B		0E00B
0E00D		0E00D
0E00E		0E00E
0E00F		0E00F
0E00H		0E00H
1455.02		1455.02
1455.021		1455.021
1455.022		1455.022
1455.023		1455.023
1455.025		1455.025
1455.026		1455.026
1455.027		1455.027
1455.028		1455.028
1455.029		1455.029

[HW1]

Hi @flying008  Can you please help me with an example workflow of the solution? I think this works best as I have provided a simple sample of more varying examples. This can be most helpful.

Thanks

[HW1]

This solution is forcing the decimal values to whatever the user defines. While I am looking for a solution that works when there are recurring decimals and that needs to be restricted.

Thanks