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

Discuss the spreadsheet application

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

Postby RC Silva » Thu Jan 23, 2020 12:45 am

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?
Last edited by RC Silva on Sun Jan 26, 2020 2:59 pm, edited 1 time in total.
Open Office 4.1 on Windows 7
RC Silva
 
Posts: 6
Joined: Thu Jan 23, 2020 12:32 am

Re: Paste numbers with leading zeros in OpenOffice Calc 4.x

Postby FJCC » Thu Jan 23, 2020 1:55 am

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?
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7750
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Paste numbers with leading zeros in OpenOffice Calc 4.x

Postby Zizi64 » Thu Jan 23, 2020 9:47 am

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?
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: 9543
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Paste numbers with leading zeros in OpenOffice Calc 4.x

Postby RC Silva » Thu Jan 23, 2020 2:17 pm

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.
Open Office 4.1 on Windows 7
RC Silva
 
Posts: 6
Joined: Thu Jan 23, 2020 12:32 am

Re: Paste numbers with leading zeros in OpenOffice Calc 4.x

Postby RoryOF » Thu Jan 23, 2020 2:40 pm

Pre-define the cells in which you wish to Paste as Text, and then Paste.
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31429
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Paste numbers with leading zeros in OpenOffice Calc 4.x

Postby Villeroy » Thu Jan 23, 2020 2:52 pm

Use format code @.
Alternatively paste-special unformatted text and when the import dialog pop up mark the column as text.
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: 28546
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Paste numbers with leading zeros in OpenOffice Calc 4.x

Postby RC Silva » Fri Jan 24, 2020 9:09 pm

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.
Open Office 4.1 on Windows 7
RC Silva
 
Posts: 6
Joined: Thu Jan 23, 2020 12:32 am

Re: Paste numbers with leading zeros in OpenOffice Calc 4.x

Postby RusselB » Fri Jan 24, 2020 9:19 pm

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.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6113
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Paste numbers with leading zeros in OpenOffice Calc 4.x

Postby Villeroy » Fri Jan 24, 2020 10:10 pm

RC Silva wrote: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
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: 28546
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Paste numbers with leading zeros in OpenOffice Calc 4.x

Postby RC Silva » Sat Jan 25, 2020 12:30 pm

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
Open Office 4.1 on Windows 7
RC Silva
 
Posts: 6
Joined: Thu Jan 23, 2020 12:32 am

Re: Paste numbers with leading zeros in OpenOffice Calc 4.x

Postby Villeroy » Sat Jan 25, 2020 1:04 pm

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.
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: 28546
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Paste numbers with leading zeros in OpenOffice Calc 4.x

Postby RusselB » Sat Jan 25, 2020 6:38 pm

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.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6113
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Paste numbers with leading zeros in OpenOffice Calc 4.x

Postby RC Silva » Sun Jan 26, 2020 2:44 pm

Thank you all for the clarifications

The problem was solved.
Last edited by RC Silva on Sun Jan 26, 2020 8:25 pm, edited 1 time in total.
Open Office 4.1 on Windows 7
RC Silva
 
Posts: 6
Joined: Thu Jan 23, 2020 12:32 am

Re: [Solved] Paste numbers with leading zeros in OpenOffice

Postby Villeroy » Sun Jan 26, 2020 3:48 pm

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.
Image

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.
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: 28546
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Paste numbers with leading zeros in OpenOffice

Postby RC Silva » Sun Jan 26, 2020 8:30 pm

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.
Open Office 4.1 on Windows 7
RC Silva
 
Posts: 6
Joined: Thu Jan 23, 2020 12:32 am

Re: [Solved] Paste numbers with leading zeros in OpenOffice

Postby Villeroy » Sun Jan 26, 2020 8:50 pm

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.
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: 28546
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests