Number forced to scientific notation

[john.vance.50]

Version: 4.2.5.2

I am copying the shipping history from my USPS account every day and pasting it into my libre office spreadsheet. This has always worked fine until recently. One of the fields is the confirmation number or label which displays on the USPS wesbite as a long integer such as 9405503699300349187201 and in the past when I pasted the table into the spreadsheet, that field would display as 9405503699300349187201. However, recently this behavior has changed and it now displays as 9405503699300350000000.

If I click on the cell and view the data in the input line, it appears as 9.40550369930035E+021 and at this point no matter what I do, I cannot convert the number back to the long number format of 9405503699300349187201 as the last eight digits have been lost.

How do I force the spreadsheet to not display the data in scientific notation 9.40550369930035E+021

Obviously, I cannot do anything to recover the original number after it is pasted into the spreadsheet. Is there some way to forbid the spreadsheet from defaulting to scientific notation. I never had this problem before the last couple of upgrades.

So, the number 9405503699300349187201 is pasting into a new spreadsheet (with no pre-formatting being done by me) as 9.40550369930035E+021 which displays in the cell as 940550369930035000000 and there is nothing I can do to convert it back as it has lost the last eight significant digits during the paste process.

Here is a link to a pdf file that shows how the spreadsheet (top) and USPS table (bottom) data appear

usps-data1.pdf

[john.vance.50]

I have found a convoluted solution, I first paste the history data into writer, where it displays the number correctly, I then put a tic mark in front of the long number, and copy and paste that into the spreadsheet with paste special and choose HTML markup language and it now paste the long number correctly.

[john.vance.50]

Wouldn’t it be easier, to just have a check box on the Paste Special function that asked
“treat long numbers as text” ?

[erAck]

Numeric precision is limited to 15 decimal digits, this has always been the case, your number exceeds that limit. To preserve all digits format cells as text before pasting the number, or on manual input precede the input with an ’ apostrophe which tells Calc to not convert the input to number but keep as textual cell content instead.

[john.vance.50]

Yes, but I am not creating the data, the USPS is creating the data. I will try to get them to put the tic mark in front of the long number, but I think putting a check box on the Paste Special function in Calc, that asked “treat long numbers as text”, would be a life saver for those of us using Calc, who do not create the data but need to import the data.

[Cerin]

The answer doesn’t solve the problem. In many cases, you can’t “preserve all digits format cells as text”, such as opening a CSV file, because Libreoffice immediately strips out the original data and replaces it with scientific notation. So even if you format it as text, it still appears as scientific notation, corrupting your data.

[erAck]

It does not. Or it does only if you format after the input has already been converted to number, which of course doesn’t change the underlying data type. If the cell was formatted to Text before input, then the input is preserved. For CSV import set the column type to Text in the import dialog.

[petermau]

It seems to me that the label is just text consisting of numbers. Could you not just define the row as text? You would still be able to compare labels or order them in ascending or descending order. I presume you are not using the fields to do actual calculations and you do not need to change the labels by adding leading zeros. to define the row as text Right click on the top of the row, select numbers and then text (right at the bottom). Perhaps the row is currently defined as scientific. … Peter

[john.vance.50]

If you define the row or column before pasting in the data as text, it does not matter because when you paste the data in, it overwrites the field back to number format.

[erAck]

Not if the cell format was Text, unless the source where you copy from includes formats, in which case you can use Paste Special (Shift+Ctrl+V) to paste without format information.

[Lupp]

That’s strange. There must have been a change in the process of copying/patsing the data. Where did you get them from? (csv attached to mail? Out of a browser page? What else?)
If you give an example we might actually see clear the reason of the malfunction.

Another thing: USPS are too damn silly to choose a reasonable format for their tracking IDs. As they are there is neither readability nor a way of syntactical recognition. Instead of asking their CEO to be fired, this is treated as an issue for (EVERY!) spreadsheet software of the world - and abused as the background for requesting a new silly smart feature to be implemented in (… how many?) applications - as if we were not already slaves of that stuff. Everything should be EXTREMELY simple for USPS and their solution would do once for all.

[john.vance.50]

The USPS did a major upgrade of their online postage service a few weeks ago and they did it on a Tuesday night and it was as usual hideous. I could not get in the first day after and it was not working correctly until late Friday afternoon. Why they choose mid week to upgrade instead of Friday evening, I do not know.

However, when looking back at earlier “cut and paste” that I had done, there is a tic mark before the long number, so that is why it worked before, but is not working now. Unfortunately, it is very hard to get a hold of tech, almost impossible. So, though I asked them to revert to putting the tic mark in, I have no idea when it will happen.

[Lupp]

Hmmm … are you sure the “tick mark” (an apostrophe?) was their gift? Ok, I still don’t know what way you got what you wanted to paste (It’s 20 years ago that I was in the US). If you ‘Paste Special’, at least, and ‘Unformatted text’ you should get an import dialogue where you can choose for the column with the 22 digits the type ‘Text’ in the label originally showing ‘Standard’. That must do the trick.