[libreoffice-users] Big decimal numbers as text

[Doug McNutt]

First post was on Sat, 29 Apr 2017 17:56:37 -0400
"Big decimal numbers as text"
And: Thank you for the replies last time. You helped get a bit further on.

'20170428153706454600(works ok) `20170428153706454600(fails)
Using menu item, insert-special character
U+60 nope
U+27 nope with exception
U+B4 nope
U+2BC nope
U+384 nope
U+1FBD nope
U+1FBF nope
U+2018 nope
U+2019 nope
U+27 seems to be an 8 bit byte but really is 0027 requiring two bytes.

I get 27 (hex, 1 byte) using the lower case of the quote mark, ", on my
keyboard.
That seems to work but it sometimes doesn't.

What has been reliable is when I perform a simple copy from a row that
has worked in the past and paste a copy into the the cell where I want
the new text string which happens to be all decimal digits.

I can then open the new cell displaying the old data and the magic
prefix. Now I can very carefully select only the digits, 19(10) of them,
and attempt to replace the old digits with the new.

The worksheet thinks it knows better and adds space characters where I
don't want them. One is at the end of the line of digits and seems to
be treated as a carriage return when the cell is closed to editing. If I
use the mouse to cut everything after the digits even though I can't see
the characters I can get what I want when I save. That's why the cell
seems to push the digits into what looks like the cell above. It's just
trying to add a new line to the top of the cell which doesn't have any
vertical space available.

Sometimes the paste from a GEDIT window acquires yet another space at
the beginning of the line of digits. It does not show in the test file
but, if you're not very careful, will somehow absorb the 27(hex) and you
see the digits but the procedure won't work the second time. You have to
remove the space without disturbing the initial character. That is hard
to do if the mouse is dirty.

If I choose a cell and declare its format to be Number-Scientific, I
copy my string of characters taken as a text string. The cell has been
declared to be a number but the system or the spreadsheet recognizes the
non numeric character and treat the paste as a string. If I remove the
first character the copy displays 2.01706241556575E+019 which is the
result of a transform into IEEE floating point. It's wrong because the
floating point has a limit of accuracy approximately 14 decimal digits,
52 bits.

I don't see any way to declare a cell as number, 63 bit binary integer
with leading sign. I suspect that LibreOffice does what Excel does; all
numbers use IEEE floating point. (Sometimes I think that also applies to
counters controlling a loop.)

There still seems to be a problem that I haven't figured out: Take a
working cell with 19 digits and the special symbol present. Open it up
as if to be edited. Place a space between the special symbol and the
first digit. Close the cell. The funny symbol U+27 is no longer
recognized as a keep-out symbol but the space is removed and the symbol
displays, on this Ubuntu box, as a lower case quote mark.

My recommendation for repair is to remove the possibility of space
characters being introduced anywhere. Do others have problems with that?
Is the spreadsheet trying to use features written for writing a book? Is
the spreadsheet trying too hard to use 16 bit super-bytes to support
non-Latin characters?

[Brian Barker]

Post by Doug McNutt
First post was on Sat, 29 Apr 2017 17:56:37 -0400
"Big decimal numbers as text"
And: Thank you for the replies last time. You helped get a bit further on.

I see only one previous (public) reply, very probably because you
hijacked another thread, so anyone not reading that thread would not
have seen your plea.

Post by Doug McNutt
'20170428153706454600(works ok) `20170428153706454600(fails)

Er, exactly: what you need is a plain, unvarnished typewriter
apostrophe - nothing else will do.

Post by Doug McNutt
Using menu item, insert-special character

Aaargh! The easiest way to get an "X" is to type it on the keyboard.
The easiest way to get a "7" is to type it on the keyboard. The
easiest way to get a "$" is to type it on the keyboard. The easiest
way to get a plain, unvarnished typewriter apostrophe is to type it
on the keyboard.

Post by Doug McNutt
That seems to work but it sometimes doesn't.

The only reason this might not work is if you have LibreOffice set to
replace single quotes (apostrophes) with smart quotes. If that is the
problem, either:
o Go to Tools | AutoCorrect Options... | Localized Options | Single
quotes, and remove the tick from Replace (perhaps temporarily), or
o Go to Edit | Undo (or Ctrl+Z) immediately after typing the
apostrophe to remove the automatic "correction".

Post by Doug McNutt
If I choose a cell and declare its format to be Number-Scientific, ...

Your problem is that you are handling something that looks like a
number but is actually (to you) a text string. Telling Calc that you
want it handled as a number is asking for trouble. Why would you do that?

Post by Doug McNutt
I don't see any way to declare a cell as number, 63 bit binary
integer with leading sign.

Again, this is not what you want. Rereading your original query, I
see that you are trying to enter long strings of digits that look
like numbers (as your example above) but which you want handled *as
text*. Stop trying to tell Calc that you want them seen as numbers!

Post by Doug McNutt
I suspect that LibreOffice does what Excel does; all numbers use
IEEE floating point.

This is irrelevant: you are handling text, not numbers. (Did I mention that?)

Post by Doug McNutt
My recommendation for repair is to remove the possibility of space
characters being introduced anywhere.

I'm not sure what you think need repairing. As to space characters,
am I not to be able to enter "Doug McNutt" into a cell - with its
helpful space? Am I not to be able to enter a telephone number, also
appropriately spaced?

Some thoughts:

1. Think of your example value above: 20170428153706454600. Is there
any sense in which this means to you (or your bank) twenty
quintillion, one hundred and seventy quadrillion, four hundred and
twenty-eight trillion, one hundred and fifty three billion, seven
hundred and six million, four hundred and fifty-four thousand, six
hundred? Surely not (or are you richer than I imagine?). If you don't
believe me, tell me what it would mean if the next label the bank
provided was exactly half this? That is the sort of question you can
ask about numbers, but not about text. These are text strings that
just happen to be formed of digits.

2. You say previous replies helped, but you make no reference to what
happened when you tried the advice to format the cells as Text before
entering values.

3. You say previous replies helped, but you make no reference to what
happened when you tried the advice to use Edit | Paste Special... (or
Ctrl+Shift+V) instead of ordinary Paste.

4. Instead of using copy and paste at all, save the source text first
as a plain text (.txt or .csv) file and use Insert | Sheet From
File... to browse to and import the data. The Text Import window
provides a very flexible system for getting data into Calc the way
you need it - even including being able to set the "Column type" to
Text. (Oh, *please* use Text for these strings of digits.) The new
data will appear on a new sheet, but it is a simple task to move it
to an existing sheet (probably using ordinary Paste this time).

I trust this helps.

Brian Barker