[libreoffice-users] Mixing LibreOffice Calc formats with ASCII text

Here are three lines copied from a LibreOffice Calc page
03/24/17    03/24/17        EPMT    p    $16.96    COMM Consumer Cellular
03/24/17    03/24/17        EPMT    p    $159.64    HOUS    Waste Management
03/24/17    03/29/17        EPMT    p    $330.65    COMM    Pair Networks

The first two columns are dates formatted using numbers/Date/Format 
01/01/04 Default to English (USA) Format code MM/DD/YY.  They are 
formatted that way for the entire length of possible columns. It's two 
columns to allow for sent and deposited dates.

The third column is empty. The fourth column is a type of payment, here 
Electronic Payment. The fifth column is one letter, here Paid, the sixth 
column F is formatted as number-currency. the rest is two more comment 
columns formatted as text.

I'm going to copy those three rows and install the contents right here:
03/24/17    03/24/17        EPMT    p    $16.96    COMM Consumer Cellular
03/24/17    03/24/17        EPMT    p    $159.64    HOUS    Waste Management
03/24/17    03/29/17        EPMT    p    $330.65    COMM    Pair Networks

Now I'm going to copy the information from the Ubuntu text editor "gimp" 
and paste them back into the spreadsheet. I select the cell at the upper 
left just below where I started. I copy from the text editor and ask the 
spreadsheet to re-enter the data.

I get an "Import" window.  It suggests separator options fixed width and 
tab. I don't understand the "fixed width". But it does show the data 
with little arrows separating the columns. If I copy from the "Text 
Import" window I get something that copies back in the text editor 
exactly as I would have expected. I will spare you seeing the same thing 
as above.

I click the button that seems to be correct for closing the Import 
window. The spreadsheet seems to have placed the entire block data into 
the three cells in the A column. Selecting the three A cells and doing a 
copy and replace into the text editor I see this:

"03/24/17    03/24/17        EPMT    p    $16.96    COMM Consumer 
Cellular"    (All in column A)
"03/24/17    03/24/17        EPMT    p    $159.64    HOUS    Waste 
Management"
"03/24/17    03/29/17        EPMT    p    $330.65    COMM    Pair Networks"

I get the same result if I first select a columns A through H before I 
do the paste.

Note that the entire lines have been honored with quote marks. The $ 
signs are part of the text but it matters not much because it's in the 
wrong column anyway. Note that the spaces are still the tab characters 
that they have been all along.

I have been trying a bunch of schemes that involve copying lines of data 
from a bank into cells over on the right side and then attempting to use 
formulas that move the data to the columns I need. I see one of those = 
signs in the front of the dates that just makes the date into text 
instead of the coded date it was. The $ sign gets left in the currency 
formatted column which declares it's just text and will fail to add with 
other data already present.

Using a multiple step procedure involving the Value() function and 
removing the $ signs can be made to work but I have to move the data 
into the spreadsheet one column at a time. Perl5 can handle the 
modifications to remove the $ and = signs when the actual source is a 
bank but I still have to use =right(8) on the dates to persuade the 
spreadsheet to accept what it put out in the first place. The likes of 
03/24/17 as text with nothing at the ends always gets something added 
that makes it into text. (Right now I'm safe from some bank offering 
3/24/17 without the leading zero. But. . .)

I'm pretty good with perl5. Does anybody have some experience in getting 
bank information into text that LibreOffice Calc will accept? It just 
takes too long to do everything one column at a time. Is the whole 
problem a bug in the spreadsheet code?


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