Download it now!
Bug 60403 - FORMATTING: Number 1.000.000 paste in Calc like it is a text
Summary: FORMATTING: Number 1.000.000 paste in Calc like it is a text
Status: CLOSED DUPLICATE of bug 80166
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: Other Windows (All)
: medium normal
Assignee: Not Assigned
Whiteboard: BSA
Depends on:
Reported: 2013-02-07 09:17 UTC by grofaty
Modified: 2014-07-17 08:07 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Print screen of Language settings and Format cells (52.16 KB, image/png)
2013-02-07 09:17 UTC, grofaty
Sample calc file after pasting numbers one by one (7.63 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-02-07 09:23 UTC, grofaty
text_import.png (42.49 KB, image/png)
2013-04-04 08:47 UTC, grofaty

Note You need to log in before you can comment on or make changes to this bug.
Description grofaty 2013-02-07 09:17:46 UTC
Created attachment 74326 [details]
Print screen of Language settings and Format cells

Problem description: 
Pasting a number with two dots (thousand and million separator) are pasted as text (dots preserved after pasting into Calc cell).

Steps to reproduce:
1. First of all I have renamed the profile C:\Documents and Settings\[user]\Application Data\LibreOffice\3 to C:\Documents and Settings\[user]\Application Data\LibreOffice\3_old to make sure this is not a profile problem.

2. Check the Tools | Options | Language Settings | Languages. My settings are "Slovenian" (see attached file for details). In my local settings dots "." are used for thousand, million, billion etc separator and comma "," is used for decimal separator.

3. Becuase of default profile all of the cells are by default formated as numbers (menu Format | Cells | Numbers tab | Category = Number.

4. Now copy the following numbers into Calc (paste into cell A1):

Note: All of the pasted numbers are correctly pasted. No problem.

4. Now copy numbers from step 3 one by one, each number in new cell.
Note: All of the numbers are pasted as numbers, except for the number 1.000.000. In "Input line" there is obvious dots are preserved in cell after pasting.

I have also tested the number 1.234.567 and the same problem.

Current behavior:
Any number with two dots does not paste correctly.

Expected behavior:
Number with two dots (thousand and million separator) should paste as numbers.

P.S. See print screen of my Tools | Options | Language Settings | Languages and Format | Cells.

Operating System: Windows XP
Version: release
Comment 1 grofaty 2013-02-07 09:23:14 UTC
Created attachment 74327 [details]
Sample calc file after pasting numbers one by one
Comment 2 grofaty 2013-02-07 17:18:47 UTC
I have tested the same problem on my Ubuntu 12.04 test computer.

=== LibreOffice v3.6.0.2 on Ubuntu 12.04 === 
Coping one number at a time (from list of numbers from bug report) and problem is exactly the same as described above for LibreOffice on Windows XP. So this looks like an old problem appearing in v3.6.0/Ubuntu and v3.6.5/Windows.

=== LibreOffice v4.0.0.3 on Ubuntu 12.04 ===
Installing LibreOffice v4 deb files from web page.
Coping one number at a time and problem is even worse then in v3.6.5.
The numbers:
are copied fine - after coping becomes number.

The numbers:
are copied with dots, so instead of numbers they become text.

So this additional problem looks like a LibreOffice v4.0 REGRESSION! to the previous LibreOffice v3.6 development line.
Comment 3 A (Andy) 2013-03-29 21:48:23 UTC
reproducible with LO (Win7 Home, 64bit)
Comment 4 grofaty 2013-03-30 06:20:45 UTC
I have retested in LibreOffice v4.0.1.2 on Windows XP and behavior is the same  as in "Comment 2" section "LibreOffice v4.0.0.3 on Ubuntu 12.04". So problem from version to got from bad to worse.
Comment 5 Eike Rathke 2013-04-02 11:50:29 UTC
I can't reproduce this neither in 3.6.6 nor 4.0.1 (Linux), in a Slovenian locale all numbers are numbers after pasting. 

Do you get the same behavior if you manually enter 1.000.000 instead of pasting?
Comment 6 grofaty 2013-04-04 08:18:47 UTC
@Eike Rathke, yes I get the same problem typing in 1.000.000.
By the way, how did you check "all numbers are numbers after pasting"? If you have pasted data in field A1, can you type in a formula in B1 like =A1+1   Do you get correct value in B1 or getting an error in cell: #VALUE!
Comment 7 grofaty 2013-04-04 08:46:36 UTC
I did another test saved the bellow values in text file:
and in LibreOffice Calc from menu File | Open and import the text file (see attachment text_import.png).
All the values imported from text file imported correctly!!! In Calc in column B I have added formula =A1+1 and copied it to check if the values are REALY numbers.
Comment 8 grofaty 2013-04-04 08:47:00 UTC
Created attachment 77409 [details]
Comment 9 Eike Rathke 2014-07-09 11:13:07 UTC
As date separator and group separator are both '.' dot in the Slovenian sl-SI locale and one of the date patterns is "D.M." (I probably failed to reset the patterns to the locale's default in my earlier attempt to reproduce this), I presume that this is related to bug 80166 and recently fixed. It appears to be fixed in my build. Please check in one of the versions mentioned there.

*** This bug has been marked as a duplicate of bug 80166 ***
Comment 10 grofaty 2014-07-17 07:46:17 UTC
Short answer: PROBLEM SOLVED with this fix.

Detailed answer on test I performed:

I did two tests on two versions of LibreOffice:
A. LibreOffice on Windows 7 (before fix was applied).
B. LibreOffice on Windows 7 (after fix was applied).

Test 1:
Copy bellow numbers to Notepad to be sure any special character is removed and then copy to LibreOffice.

Test 2:
Save bellow numbers to Notepad and save the file as mytest.txt


Test 1 and Test 2 using system A:
First two numbers are correctly pasted as numbers so they become 1000, all other numbers become text. So problem exists.

Test 1 and Test 2 using system B:
All numbers are correctly pasted and opened. So problem is solved.

Until now I was forced to do the Find-Replace and search for dot and replace with empty string, this was time consuming and annoying. Thanks a million for this fix. This is one single fix most important fix that will save a lot of my time.
Comment 11 Eike Rathke 2014-07-17 08:07:22 UTC
Great, thanks for verifying.