Finaly Edition: Swap Commas & Periods in Thousands Separators & Decimal Numbers

I guess everyone who had worked with calc, has tried to get hold of a table which is implemented in reverse in terms of commas and periods as respectively thousands separator and decimal number than desired

I’m looking for the ultimate “canonical” and simplest ideal solution to swap periods with commas and vice versa, already when you insert a foreign table’s (“wrongly formatted”) numbers (and text) in your own sheet, AND so that Calc understands it as a decimal number with thousands separators

I did some research and couldn’t immediately find a simple solution

So its (from my point of view):
1 Change commas as thousands separators to periods.
2 Change periods in decimal numbers to commas (preferably in the same workflow)

Like this :


Or (The opposite point of view) 25.200 to 25,200 (and not 25,2)

When pasting into Calc, you should get the text import dialog. In the language field of that dialog (near the top), select a locale which uses the number format you are pasting. (Like English (USA)) This should have your numbers properly interpreted.

You will need to apply the desired formatting (to add thousands separators) as a separate step, which can be done before or after pasting.

Sometimes you are not automatically sent to the text import dialog when pasting. Use “paste special” and select the dialog manually.

And to fix already entered content, the proper advice is of course …

Good point!

1 Like

Use Text to Columns.