Correct Decimal Place

[Sakhile]

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.

[Tiffyyy · Accepted solution]

Hi @Sakhile ,

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.

[PhilipMannering · Accepted solution]

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

SubString([CPK], 1)

[IraWatt]

Hey @Sakhile,

Is CPK supposed to be a number/decimal?

[Sakhile]

@IraWatt its supposed to be a number. 

[IraWatt]

@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