From comma to period within formula

[ramseydk]

Hi,

This should be simple, but I have not been able to find a solution that works for me yet.

I am pulling out numbers from a CSV file originated in Australia. This means that numbers are in this format, for instance: 1,500.45

This format is considered as text in my Excel version (regional settings for Denmark), so I am able to use a formula such as =VALUE(REPLACE(A1;FIND(".";A1;1);1;",")) to convert the text into a number properly formatted with two decimals for my region, i.e. 1.500,45

My question is, how do I go the other way? When I have a number in the format 2.900,25 but would like it to be 2,900.25?

I have tried making my number into a text string, but I just can't get the "," and "." to get in there properly.

I want the transformation to be made in a formula. I do NOT want to have to do any formatting of the cell, or changing of regions, or search and replace (unless the later takes place inside of a formula).

thanks a lot in advance.

 

[AlphaFrog]

Try this...

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

 

[ramseydk]

Thanks, I tried that already... and many combinations thereof. All I get with =TEXT(A1,"#,##0.00") is the result: 2900

So no commas or periods at all, and it also cut off the two decimals. It doesn't change anything if I try to change the formatting of this value either.

 

[kvsrinivasamurthy]

Try this

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

 

[ramseydk]

thx. I tried the substitute formula and I get the exact same result as with the Text formula, ie. no periods/commas at all (see screenshot)

 

[kvsrinivasamurthy]

Try this

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