# From comma to period within formula

#### ramseydk

##### New Member
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).

### Excel Facts

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

#### AlphaFrog

##### MrExcel MVP
Try this...

Excel Formula:
``=TEXT(A1,"#,##0.00")``

#### ramseydk

##### New Member
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

##### Well-known Member
Try this

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

#### ramseydk

##### New Member
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
13.6 KB · Views: 4

#### kvsrinivasamurthy

##### Well-known Member
Try this

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

Replies
5
Views
76
Replies
1
Views
100
Replies
1
Views
201
Replies
7
Views
259
Replies
3
Views
224

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.

### Which adblocker are you using?

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

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