ToNumber() partially works. Please help

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

Example.yxmd

Workflow

Output

Parse

Best Practices

Data Investigation

Accepted answers

All comments

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

Example.yxmd

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.

Example To Number.yxmd

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.

Rounding.yxmd

Raj

please use round function as it will derive the required output

sol.png

KrishnaChithrathil

@HW1

hey. I think this would work.

ToNumber() partially works.yxmd

Quick Links

This months top contributors

AkimasaKajitani 388

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335