[Solved] From Dot to Comma when Copy/Paste

[nophoi]

Hello guys,
I have to copy and paste a lot of data to my Calc (A, B, C, D). Then I need to sum them in other cell (E).
These data use the dot as decimal and my sum works if there's the comma instead. (I live in Europe and I use €).

How can I solve this?

Cattura.png (4.2 KiB) Viewed 2513 times

Code: Select all

E1  > =A1+B1+C1+D1
E2  > =A2+B2+C2+D2

Thanks!

[Zizi64]

(I live in Europe and I use €).

In which country you live in Europe (what localizaton settings are you using)?



Please upload an ODF type sample file here.

[nophoi]

I live in Italy.

Settings:


I also attached the file

[Zizi64]

Your numbers in the sample file are strings really. However the cells are formatted as Number.
When you formatted the cells? Before/After data pasting?

How you pasted, inserted this data into the document? Did you set the Import Filter properly?

[nophoi]

I didn't touch the category of the cells. They were "Numbers > Standard" before pasting.

Also I pasted normally. I don't know the "Import Filter".

[Zizi64]

Also I pasted normally. I don't know the "Import Filter".

Use the Paste Special - as unformatted text (instead of the simple paste), and set the import Filter. Then the numeric-like data will be recognized as numbers.

[Villeroy]

Italia is a comma locale.
Edit>Find&Replace...
FInd: .
Replace: ,
[More Options]
Regular expressions: OFF

-0,06 0,00 -0,11 9,00
-0,12 0,00 0,00 -0,05

Call menu:View>Highlight Values and numbers are highlighted in blue, formulas are green.

If you choose "English" in the import dialog, everything should import correctly. "English" becuase the numbers are English

[nophoi]

Also I pasted normally. I don't know the "Import Filter".

Use the Paste Special - as unformatted text (instead of the simple paste), and set the import Filter. Then the numeric-like data will be recognized as numbers.

Paste_Special.png

Sorry, I don't understand how to set the import filter.

A) Right Click > Paste Special > Unformatted Text



B) but then it doesn't show the Text Import Window

Italia is a comma locale.
Edit>Find&Replace...
FInd: .
Replace: ,
[More Options]
Regular expressions: OFF

-0,06 0,00 -0,11 9,00
-0,12 0,00 0,00 -0,05

Call menu:View>Highlight Values and numbers are highlighted in blue, formulas are green.

If you choose "English" in the import dialog, everything should import correctly. "English" becuase the numbers are English

Hi, sorry I only understand the "Find&Replace" solution, but I can't do it because there will be other data (no numbers) in the file.

[Villeroy]

Edit > Find&Replace ...
[More Options]
Current Selection Only = YES

[Zizi64]

B) but then it doesn't show the Text Import Window

The import filter only will be appeared, when you try to paste more then one rows of the data in same time.

[Villeroy]

You get an import dialog when you copy this from your browser or from a text editor:

Code: Select all

-0.06
-0.11
9.00
8,83
-0.12

and paste unformatted text or HTML.
When you see such a dialog with a language selector, choose "English" because of the decimal points.
If your data include US American dates such as "12/31/1999", then choose "English(USA)" in order to import the text as correct date values.

[nophoi]

Zizi64 & Villeroy

Thank you so much! They both work
I will put [Solved] in the subject line.