[Solved] Paste numbers with leading zeros in OpenOffice 4.x

[RC Silva]

Hello

Work with inventory control. The products have codes like 0008 and 0.008. It used OpenOffice Calc 3.2, formatted the cells as text, copied and pasted the codes and the zeros didn't disappear. Now with version 4.1.6, they disappear.

Look:
In OpenOffice Calc 3.2, it looked like this:
A1 0008
A2 0015
A3 0.005
A4 0.150

In OpenOffice Calc 4.1.6, it looks like this:
A1 8
A2 15
A3 5
A4 150

Here, 0008 is different from 0.008. If it is in user-defined format and type 0000, OpenOffice Calc keeps the original formatting, but in the column there are also numbers with the format 0.000. That way, I will spend a lot of time correcting formatting.
Please, you can change the current OpenOffice Calc to leave it as it was in version 3.2, when it was possible to copy and paste numbers with leading zeros, as text, without missing the zeros.
There is something even worse. In the current OpenOffice Calc, paste the number 0.59 as text, depending on the language you select, it is rounded to 0.6. What is the reason for this?

[FJCC]

It is possible to paste text like 0008 into the latest version of OpenOffice and keep it as text. What steps are you doing to paste the data? What is the source of the data and what key strokes or menus do you use?

[Zizi64]

Now with version 4.1.8, they disappear.

There is not 4.1.8 version today...
https://openoffice.apache.org/downloads.html

Are you using LibreOffice 4.1.8?

[RC Silva]

Correction: It is OpenOffice 4.1.6, Brazilian Portuguese language.

In short: I want to paste numbers with the format 0000 and 0.000 in OpenOffice Calc. These are product codes. For example:

0008
0150
0.320
0.005

If you paste the codes as text in OpenOffice Calc, the leading zeros will disappear.

In OpenOffice 3.2 it was possible to paste these numbers as text, the format was maintained. In the current LibreOffice Calc too. The problem is not with the language of OpenOffice, nor with the inventory control software.
If you type 0.500 and 0.500 in OpenOffice Writer, WordPad, and paste in OpenOfice Calc, the leading zeros disappear.

I partially solved the problem. The procedure goes like this:

“Format> Cells> User defined> Format code”. I type 0000. Then “Edit> Paste special> Plain text”. But in the column there are numbers with the format 0.000.

[RoryOF]

Pre-define the cells in which you wish to Paste as Text, and then Paste.

[Villeroy]

Use format code @.
Alternatively paste-special unformatted text and when the import dialog pop up mark the column as text.

[RC Silva]

I use numbers from 0000 to 9999 and 0.000 to 9.999 (not a comma). I believe that recent versions of OpenOffice Calc have a bug. If you just select cell A1 and then go to Format> Cells> Numbers> Category> Text> Format @ and then Edit> Paste Special> Plain text and paste 0008, it will be 0008. But if you select A1 through A5, for example, and paste a sequence of numbers, they will lose the leading zero. For example:
0001
0002
0003
0004
0005

0001 becomes 1

In LibreOffice they do not lose the leading zero.

[RusselB]

OpenOffice is pasting the significant digits when pasting a number. When dealing with mathematics this is expected.
If anything, I would consider the bug to be in LibreOffice not OpenOffice based on the information you have supplied regarding the digits that are pasted when pasting a number.

[Villeroy]

In LibreOffice they do not lose the leading zero.

Every spreadsheet does this. I just tried LO 6.0 and LO 6.3. Excel does it. OpenOffice does it.

Either you use paste-special and mark the column as text or you format the target range as text (number format code @).
If you need the numeric value, format the cells with leading zeroes

[RC Silva]

But that was the behavior of OpenOffice 2.0.1, for example. I select cells A1 through A5 at once and format them as text. Then I paste the numbers as unformatted text, all at once, the zeros don't disappear. If you do the same thing in OpenOffice 4.1.6, the zeros disappear, it seems not to be allowed. You need to format one cell at a time as text and paste as unformatted text one number at a time so that the leading zeros do not disappear.
I believe the change happened in OpenOffice 3.3.
Look at this post. It must be from someone who used OpenOffice version 3.3 or later. It also reported the difficulty of pasting numbers with leading zeros or a strange shape.

https://stackoverflow.com/questions/138 ... put-data-n

[Villeroy]

May be there were too many complaints that a calculator software imported text instead of numbers? I don't remember. No matter what you do by default, you will always do it wrong for some group of users who is too lazy to override defaults.

[RusselB]

I used software years ago, which I won't name as that would advertise the other software, where there was an option in the Paste Special that allowed non-significant digits to be pasted as part of the numeric entry.
Kind of neat, but I don't know if that's still the case for that software, as we are talking over 4 decades ago.

[RC Silva]

Thank you all for the clarifications

The problem was solved.

[Villeroy]

If the numeric value does not matter (product IDs, phone numbers etc), just mark the column as text when importing unformatted text from clipboard or from a text file.


Reading again your second posting in this topic, I think you import digits with points in non-English context with the point being the thousands separator as in 1.234.567 (English: 1,234,567). Calc ignores thousands separators, so 0.150 becomes number 150 and 0.005 becomes number 5. No number formatting trick will ever change 5 to 0.005. The cell value remains 5 even if your formatting trick somehow manages to display 0.005. Wrong values looking right is a real danger when using spreadsheets.

If the point is supposed to be a decimal point, choose English as import language and check "detect special numbers" as shown in the picture.
If the numeric value does not matter and you just want to import the exact text with digits and points, mark the column as text as column #2 in the picture.

[RC Silva]

Villeroy

Your answer is perfect.
My solution is very precarious.
It really was that.
Your answer is also for those who paste numbers with zeos to the left in OpenOffice Calc. Just go to “Edit> Paste special> Plain text, in Fields, touch the column, under“ Column type ”, choose the“ Text ”option. Numbers do not lose leading zeros.

[Villeroy]

Spreadsheets are simplified programming languages with 2 major types of data: A cell value is either text or number unless it is an error. Most users don't get the difference between the text "00123" and the number 123 which may be displayed as 123, 00123 or in thousands of different ways. The text "00123" is always a different value than the number 123 even when the number is displayed as "2 May 1900" which is the same value as 123 (123rd day after day zero).

In order to make things more "user friendly" (which usually means more complicated), Calc supports more than 100 of different locales. A locale defines how numbers are displayed and if some input is a number or not. Things become very complicated when users import text from various sources expecting that Calc applies some kind of artificial intelligence which is not existent.