Re: [libreoffice-users] Big decimal numbers as text

At 17:56 29/04/2017 -0400, Doug McNutt wrote:
> I'm having a problem, once again, for copy and paste from US Bank 
> downloads into my spreadsheet ...
> 20170428153706454600 and 20170425154724510819 are two "numbers" that 
> the bank uses as unique flags that need to be saved ...

Your scare quotes betray that you are getting the point that these 
are not numbers at all, but merely text strings that happens to be 
composed only of digits. The way to tell that they are not numbers is 
to ask yourself what would be the point or effect of adding them 
together or subtracting one from the other - the very sort of things 
that you do with numbers. (Answer: None at all.)

> Note the apostrophe-like character that I have added at the beginning.

That is necessary only if you are *typing* numeric strings into your 
spreadsheet. (And it should be an apostrophe, not an "apostrophe-like 
character" - whatever that is.)

> Precision of 64 bit floating point forbids allowing Calc to convert 
> the sequence to a floating point number when I copy and paste.

Well, you don't want these strings to become numbers anyway.

> The number gets placed in the cell in a way that cannot be seen 
> because it is drawn in space that is above the top of the cell. 
> Increasing the height of the row makes it appear.

I'm guessing that you are somehow copying not just the numeric string 
but some trailing character representing a line or paragraph break. 
What happens if you
o double-click the cell to allow editing within the cell,
o move the cursor to the end of the cell contents - on the space 
under the text itself, that is, and
o press Backspace?

> Can anyone provide a procedure that will always work?

Yup.

o First, format the cells to receive this data as Text. You want 
text, not numbers. Choosing the appropriate format is one of the 
first things you should do in a spreadsheet. Surely all these tokens 
will go into a single column that contains nothing else? So you need 
merely to select the column header and set the format to Text.

o When you paste in the data, use Edit | Paste Special... (or 
Ctrl+Shift+V) instead of ordinary Paste and choose "Unformatted 
text". In the unlikely event you see the Text Import dialogue, click 
the column header under Fields and change "Column type" from Standard to Text.

> I still have problems with mm/dd/yyyy dates which sometimes get 
> apostrophes added to them by the Calc software.

This behaviour varies with your locale and other settings. Remember 
to set cell (or column) format appropriately first. Are you again 
pasting in extraneous characters?

> Does Calc save dates as floating point 64 bit words?

Spreadsheets save dates as numbers - the number of days from a date 
origin - but displays them according to your chosen format.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted