From comma to period within formula

ramseydk

New Member
Joined
Jan 12, 2015
Messages
25
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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

ramseydk

New Member
Joined
Jan 12, 2015
Messages
25
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.
 

ramseydk

New Member
Joined
Jan 12, 2015
Messages
25
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)
 

Attachments

  • comma.png
    comma.png
    13.6 KB · Views: 4

Watch MrExcel Video

Forum statistics

Threads
1,127,031
Messages
5,622,308
Members
415,891
Latest member
Oksana88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top