How to change dots to commas in Numbers

Let's say you are in a region that uses , as a decimal separator. Then taking of sum of them doesn't work.

But you can change to a region that uses the period/dot . as decimal separator.

Then change the data format to Number, and you've got a working sum:

Then switch back to your region that uses , as a decimal separator,

And the sum now works with the decimal separator now a , :

You can of course go the other way too, from data with , to a region and data with . as a decimal separator.

This sounds complicated but actually it's a few quick steps.

SG

You cannot make a sum out of "numbers" with dots in them because they are not actually numbers (or are not the correct numbers). If you are in a region that uses a comma for the decimal separator, a "number" containing a decimal point may be interpreted several different ways if the cell was not pre-formatted as text.

1.23 becomes 01.23 which is 1:23AM

1.234 becomes the number one thousand two hundred thirty four (wrong number)

123.4 is the text "123.4"

If the cell/column/row/table is pre-formatted as text, the result will be text no matter what. But text "numbers" do not work in most functions, like SUM. SUM ignores text.

I do not know how the numbers with dots came to be in your table or if they are text or a mix of formats. If you select them all and change the format to text, they should all become text. After changing the format, click elsewhere then select all those cells again and make sure the format stuck. It should be text, not "multiple" or something else. All of the values should be aligned to the left of the cells.

From here you have a choice. Both require the "numbers" start out formatted as text.

Option 1:

1. Change the region to a place that uses a dot.
2. Change the format of those cells to Number. They should now align on the right side of the cells.
3. Change the region back to what it was. All the numbers should have the dot automatically replaced by a comma.

Option 2:

If this is a column of "numbers", you can use another column to replace the dots with commas. Let's say your "numbers" are in column C

1. If none of the numbers have a comma as a thousands separators, the formula in each row of D =VALUE(SUBSTITUTE(C; "."; ","))
2. If the numbers may have commas as thousands separators you have to remove the commas before turning the dot into a comma. The formula in each row of D =VALUE(SUBSTITUTE(SUBSTITUTE(C; ","; "");".";","))
3. Use the numbers in column D or Copy them and Paste Formula Results into column C, then delete all the formulas in column D.

For option 2, you could have a table all set up on another sheet to do this. You would start by copy/pasting your column of "numbers" into it. The formulas in that table will convert them to Numbers that you can Copy/Paste Formula Results.