Correct Decimal Place

Hi Guys,

Hope you are well.

May you please assist me with the following data cleansing.

I want to move the comma into the correct position after the first digit in each row. Some rows already have the comma in the correct position but i need a way to tackle the ones that dont have a comma and the ones that do however the comma is in the wrong position

Example:

I need to correct 753,45 to 7,5345

and correct 345 to 3,45.

In summary: move the comma to the second position after 1st number.

Please do this for all data in the column.

CPK Test data.xlsx

Data Investigation

Accepted answers

I first removed all the comma using the "DATA CLEANSING" tool, then add in the comma in the second position using a "FORMULA" tool.

Solution as attached. Let me know if it resolved your issue.

Solution_CPK.yxzp

philipmannering

You can also simplify the above by not specifying the length of the string. That is,

SubString([CPK], 1)

All comments

I first removed all the comma using the "DATA CLEANSING" tool, then add in the comma in the second position using a "FORMULA" tool.

Solution as attached. Let me know if it resolved your issue.

Solution_CPK.yxzp

philipmannering

You can also simplify the above by not specifying the length of the string. That is,

SubString([CPK], 1)

Is CPK supposed to be a number/decimal?

@IraWatt its supposed to be a number.

@Sakhile If its a number the comma is purely aesthetic and changeable from the user settings:

To change CPK to a number you will need to remove the text "," using a formula tool. Then a select tool can make it a number data type:

(the double data type is a number with decimal points)

Any questions or issues please ask
HTH!
Ira

Quick Links

Tags

This months top contributors

atcodedog05 19458

Qiu 15865

MarqueeCrew 13708

apathetichell 13703