Cleansing decimal values

[beatrizmguerreiro]

Hello, 

I have a dataset that contains string values with decimal places.

There are values with decimal places represented with a comma (,) or a dot (.). There are also values with an extra "-" at the end. And lastly, values with 1 or more decimal places.

I need your help to format this dataset by setting up the following:

- clean dataset, without the commas setting up the thousands - i wish to have the full value;

- 2 decimal values;

- remove the commas and the "-".

917,63

4,921,660.4

184,296.08-

220,929.66

4,785,338.86

542,283.22-

Thank you.

[AngelosPachis · Accepted solution]

Hi @beatrizmguerreiro ,

You can do all of the above with a formula tool

I kept the field type as a string, but you can go on and change that to a double, float or fixed decimal.

Only note is for record 2, there is 1 decimal place (4) because the next would be zero, so the value is 4921660.40. Wasn't sure if you wanted the zero to appear there.

Hope that helps,

Angelos

[beatrizmguerreiro]

Hello Angelos,

Perfect, thank you so much! 

Yes, in record 2, I wish to have the 0. 

[AngelosPachis]

Hello @beatrizmguerreiro,

I added 2 extra formulas in there to make it easier to explain and hopefully make it more understandable.

In the first one I'm just parsing the characters(digits) that come after the decimal point. Then in the final expression, if the length of those decimal places is less than 2 (so that means I am missing 1 or more zeroes theoretically), pad that field with zeroes on the right.

Let me know if you have any questions and I will try to come up with a better example if something is unclear.

Regards,

Angelos