Replace third character from right with other character

[Tommeck37]

Hello,

Can anyone help with this task

I have figures in strange format : 1.500.250,20 which should be 1,500,250.20.
Do you know the way to replace points with commas and commas with dots?

Ctrl+H replaces all at one go so you either have all points or all commas. But the thing is to have the third character from right replaced with point.

I am lost. Can you shed the light?

Cheers

Tommeck37

 

[gaz_chops]

If a formula is ok try

=SUBSTITUTE(SUBSTITUTE(A1,".",","),",",".",3)

 

[petereddy]

Use a dummy search and replace:
","->"H"
"."->","
"H"->"."
Would that work?

 

[JimSnyder]

Just adding for another option, build a new string from substrings putting the separators in between.

 

[Tommeck37]

Hello,

Thank you guys for your help.

SUBSTITUTE formula works fine but only for this specific example. However, I need to apply the change to various figures which has fewer or more digits than that in example.

That's why petereddy's way suits well for my problem. I change first points into letter and then comma into point and letter into comma.

Thank you very much !

 

[Rick Rothstein]

If your values will always have two decimal places after the conversion, then you can use this formula...

=TEXT(SUBSTITUTE(SUBSTITUTE(A1,".",""),",","."),"#,##0.00")

 

[Tommeck37]

Thank you Rick. It works.

 

[Rick Rothstein]

If your values will always have two decimal places after the conversion, then you can use this formula...

=TEXT(SUBSTITUTE(SUBSTITUTE(A1,".",""),",","."),"#,##0.00")

And if the number of digits after the decimal point in the converted value can vary, then use this formula instead...

=TEXT(SUBSTITUTE(SUBSTITUTE(A1,".",""),",","."),"#,##0"&IF(COUNTIF(A1,"*,*"),"."&REPT(0,LEN(A1)-FIND(",",A1&",")),""))