Cleansing decimal values

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.

Tips and Tricks

Best Practices

Preparation

Accepted answers

AngelosPachis

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

example.yxmd

All comments

AngelosPachis

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

example.yxmd

beatrizmguerreiro

Hello Angelos,

Perfect, thank you so much!

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280