ToNumber() partially works. Please help

[HW1]

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

[christine_assaad]

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!

[HW1]

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

[christine_assaad]

@HW1 

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.

[gabrielvilella]

What if you use a Round formula? 

Round(ToNumber([FieldName]),0.001)

[DenisZ]

Please see proposed solution to the problem. If you want more decimals, just change in the formula tool to "0.001" or any of your liking. 

Please mark as solved if it solves the issue. 

[Raj]

please use round function as it will derive the required output

[KrishnaChithrathil]

@HW1 

hey. I think this would work.