[Solved] From Dot to Comma when Copy/Paste

Discuss the spreadsheet application

[Solved] From Dot to Comma when Copy/Paste

Postby nophoi » Sun May 10, 2020 10:29 am

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
Cattura.png (4.2 KiB) Viewed 404 times

Code: Select all   Expand viewCollapse view
E1  > =A1+B1+C1+D1
E2  > =A2+B2+C2+D2


Thanks!
Last edited by nophoi on Sun May 10, 2020 2:16 pm, edited 1 time in total.
LibreOffice 6.3.5.2 on Windows 10
nophoi
 
Posts: 16
Joined: Sat May 09, 2020 3:41 pm

Re: From Dot to Comma when Copy/Paste

Postby Zizi64 » Sun May 10, 2020 12:06 pm

(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.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9542
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: From Dot to Comma when Copy/Paste

Postby nophoi » Sun May 10, 2020 12:13 pm

I live in Italy.

Settings:
Image

I also attached the file :)
Attachments
testabcd.ods
(9.27 KiB) Downloaded 16 times
LibreOffice 6.3.5.2 on Windows 10
nophoi
 
Posts: 16
Joined: Sat May 09, 2020 3:41 pm

Re: From Dot to Comma when Copy/Paste

Postby Zizi64 » Sun May 10, 2020 12:18 pm

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?
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9542
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: From Dot to Comma when Copy/Paste

Postby nophoi » Sun May 10, 2020 12:44 pm

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".
LibreOffice 6.3.5.2 on Windows 10
nophoi
 
Posts: 16
Joined: Sat May 09, 2020 3:41 pm

Re: From Dot to Comma when Copy/Paste

Postby Zizi64 » Sun May 10, 2020 12:50 pm

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
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9542
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: From Dot to Comma when Copy/Paste

Postby Villeroy » Sun May 10, 2020 12:53 pm

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: From Dot to Comma when Copy/Paste

Postby nophoi » Sun May 10, 2020 1:09 pm

Zizi64 wrote:
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

Image

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

Image


Villeroy wrote: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.
LibreOffice 6.3.5.2 on Windows 10
nophoi
 
Posts: 16
Joined: Sat May 09, 2020 3:41 pm

Re: From Dot to Comma when Copy/Paste

Postby Villeroy » Sun May 10, 2020 1:10 pm

Edit > Find&Replace ...
[More Options]
Current Selection Only = YES
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: From Dot to Comma when Copy/Paste

Postby Zizi64 » Sun May 10, 2020 1:11 pm

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.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9542
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: From Dot to Comma when Copy/Paste

Postby Villeroy » Sun May 10, 2020 1:19 pm

You get an import dialog when you copy this from your browser or from a text editor:
Code: Select all   Expand viewCollapse view
-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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28544
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: From Dot to Comma when Copy/Paste

Postby nophoi » Sun May 10, 2020 2:16 pm

Zizi64 & Villeroy

Thank you so much! They both work :)
I will put [Solved] in the subject line.
LibreOffice 6.3.5.2 on Windows 10
nophoi
 
Posts: 16
Joined: Sat May 09, 2020 3:41 pm


Return to Calc

Who is online

Users browsing this forum: RusselB, Zizi64 and 19 guests