At 20:05 19/04/2014 +0200, Marco Fioretti wrote:
I have the problem below with LO 126.96.36.199 on a Fedora box. I have a
shell script that generates and save to a text file called
synthesis.csv many lines like this:
Calc loads that file without problems, when tell it to use | as
field separator. Lets assume the payments imports (-100.25 and
-50.25) end up in cells F1 and F2. if I write in a third cell the
formula =sum(F1:F2) I get zero, because F1 and F2 are seen as text.
Your problem is there, of course. I cannot reproduce this if I "load
that file": in that case, the values are correctly interpreted as
numbers. Are you perhaps instead pasting the material into an
existing sheet? In that case, the existing cell format of the sheet
comes into play. If column F is formatted as text, that's how your
values will be interpreted.
OK, so I select those cells and do Format->Cells->Numbers->Number =
-1234.12. But I still get zero, because the operation above adds to
the content of \both F1 and F2 a single quote prefix:
F1 = '-100.25
F2 = '-50.25
which, right now, I have to remove manually to make the formula work.
It's important to realise that changing cell formatting changes only
the way data is displayed: it doesn't change the data itself. So your
values are still text. In order to make this clear, an apostrophe is
helpfully prepended in the Input Line to values that look like
numbers. When you remove those manually, you are asking Calc to
reinterpret the values. Now that the cells are formatted as numbers,
this will - as you need - create numbers from your text values.
What is happening, and how can I fix it?
If you cannot solve the problem at source, there is a simpler workaround:
o In an unused column, insert the formula =VALUE(F1) in row 1 and
fill it down the column.
o Copy the column and paste it back over the source column, but using
Edit | Paste Special... (or right-click | Paste Special... or
Ctrl+Shift+V) instead of ordinary Paste. In the Paste Special
dialogue, remove the tick from "Paste all" and ensure that Numbers is
ticked but Formulas *not* ticked.
Even better: how to make LO automatically format and use strings in
the format -1234.12 as numbers when it opens the file?
You seem to be writing from a country where the normal fractional
separator would be a comma, not a point. Is this a locale issue?
I trust this helps.
To unsubscribe e-mail to: firstname.lastname@example.org
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
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy