Replace last comma with dot

[mohan2391]

Hi,

i have a field data like below

1,11,111

22,222

3,33,333,3333

456,123,789,000,012

i want output like below

1,11.111

22.222

3,33,333.3333

456,123,789,000.012

I mean only the last comma (,) in the value should be replaced with dot(.)

If there are 3 commas in the field value, 1st 2 commas should be there but the 3rd comma should be replaced with dot

How to achieve this?

[olivierrobin]

hello

you may try something like this :

let a='1,234,567';
let b=index(a,',',-1);
let a =left(a,b-1)&'.'& mid(a,b+1);
trace *** $(a) ***;

[mohan2391]

I'm loading data from excels. While loading itself i have to do this

[olivierrobin · Accepted solution]

you can put the formulas in the load

load left(left(a,index(a,',',-1)-1) & '.' & mid(a,index(a,',',-1)+1) as new_column

[d_pranskus]

Hi

You can use the following snippet:

Suppose the column in Excel is Amount

LOAD

     LEFT(Amount, INDEX(Amount, ',', -1) - 1) & '.' & MID(Amount, INDEX(Amount, ',', -1) + 1) AS Amount2

     ....

[mohan2391]

ThankYou!!

[olivierrobin]

you' re welcome

remember to close the post

[mohan2391]

A small correction needed in this

no need of 2nd left()

By mistake it came i think

[olivierrobin]

you're right

too quick for copy / Paste