String to Numbers and decimals?

Hello,

I hope someone can help me with the following :

I have a dataset where I have useless zeros before the numbers.

I tried the Trim left function for the ID but I am not sure if I can do this with a big dataset so I am not sure how many zeros I need to get rid off.

As for the amounts, I tried to transform it into FixedDecimals, it didn't work either (NULL values) and not sure If I can trim the zeros since I don't know what's the maximum value (since the dataset will be updated)\

Thanks for your help 🙂

test_data.yxdb

Datasets

Preparation

Accepted answers

RolandSchubert

Hi @adina ,

I think, the STRSPN function could be useful. I've attached a sample workflow using a Multi-Field Formula to convert the numbers (removing leading '0' using SubString and STRCSPN and changing the field type to Fixed Decimal) and a Formula tool to remove '0' for Client_ID. Hope this ishelpful.

Best,

Roland

StringToNumbers.yxmd

All comments

patrick_digan

@adina I would use a select tool and convert them to double:

Community 2020-04-03.yxmd

RolandSchubert

Hi @adina ,

Best,

Roland

StringToNumbers.yxmd

adina

Thank you Roland, this is exactly what I was looking for!

adina

Hello again Roland,

I've just noticed by changing the type of numbers to fixed decimals, the sums are now different than the original ones (the minimum and the maximums for example). Do you have any idea what might be wrong? at the beginning I had a min of -000000.015 now I have something around -006754832.8

Thanks again!

Goddenra

Apologies for resurrecting an old thread but I was searching for a solution for exactly the same problem, as when I was converting to Fixed Decimal is was converting to Null.

Tried multiple different workaround but only the ToNumber function in a formula consistently worked for me.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703