I have a string column that contains numbers with repeating decimals. I need to convert the numberical values to numbers with non repeating decimal numbers. I cannot use the select tool to convert as there are numbers e.g. 1608.09000001 to be read as 1608.09 which the formula does as expected but not with all.
I can convert most of it with ToNumber() however it does not work for all the numbers.
e.g 1528.24599999999 needs to be 1528.246 but it does not convert
Can anybody please help me convert it?
Also, why does it not work for all the numbers?
Please find example workflow attached
Hi @HW1
I created the column String. Hope this is what you are looking for.
Formula:
IF REGEX_Match([Customer Number_Old], "\D")= -1 then [Customer Number_Old] else REGEX_Replace([Customer Number_Old], "(\d+\.\d{3})(.*)","$1") endif
Cheers!
Thank you @christine_assaad It converts every repeating decimal value to a 3 digit decimal which is an issue.
e.g. 1011.0700000000001 is expected to be converted to 1011.07 however your formula converts to 1011.070
Which cannot join to another table with the same value further down the workflow (not shown here)
Please find the updated example workflow with the value attached
I split the data as "TBA" won't make the Col Numeric so I changed the data type then stacked the data back using Union. Hopefully that helps.
What if you use a Round formula?
Round(ToNumber([FieldName]),0.001)