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.
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.