Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index

On Mon, 2013-11-11 at 02:56 +0200, Ady wrote: 
I have a spreadsheet that is the output of a SQLite Database on Android. It contains numbers 
that have been formatted as text. When I copy the columns into my LibreOffice Spreadsheet I 
want to make sure that they are interpreted as numbers. 

However they come in as text and the manuals and help at Libre Office are less than useful. implies that this conversion 
happens automatically but it sure doesn't

I found reference to a value function, but no information on how to apply it to this data.

 I tried just changing the format of the cells to be number but that didn't do anything at all. 

I've also attempted various styles of paste special but still it doesn't work. 

There has got to be a way to quickly say this text is all really numbers and get it working!

Please help

If the numbers (formatted as text) are already saved in your 
spreadsheet, I would normally suggest a simple procedure involving 
"paste special" and multiply (by "1"). This has worked for me in 
several other spreadsheet tools.

But Calc will (currently?) fail, because Calc adds a single quotation 
mark at the beginning of the cell. So what seems to be just "1" 
(without the double quotation marks), in Calc actually is "'1" 
(without the double quotation marks, but including the single initial 
single quotation mark). If you currently don't see the initial single 
quotation mark, you could see it (in the formula bar) by copying one 
of these cells and pasting it in a new one (among other options).

This single quotation mark will even remain after changing the format 
from 'text' to 'numbers', so this is what makes the solution less 
than simple.

You could select the relevant cells, change their format and then 
'find and replace' on that same selection. But, since this is a 
special (hidden) character, I'm not sure how to make it happen ('find 
and replace' might not find the specific character).

As a simple user, I see this "hidden" addition of the initial single 
quotation mark as a _BUG_, and as one of those basic "features" that 
work poorly in LibreOffice Calc than in several other spreadsheet 
tools. I don't know if this behavior can be "corrected" or improved.

Now, if your data is not yet imported into Calc, you can change the 
type of data from "standard" or "text" to "numbers" during the 
'import' procedure. This task is simple enough if the numbers are 
already located under the same "column" in the csv / text file that 
you use as source to import the data into Calc.

All the above comments are relevant only if you don't need to do the 
conversion in a repeatedly, scripted / batch / automatic way.



If you are importing a txt or csv file into Calc you can use INSERT >>
SHEET FROM FILE. The first part of the wizard will you can click OK. The
second dialog has a section "Other Options". In this section check
"Detect Special Numbers". There is a preview screen which shows what the
data will look like. If the column has the same data type (numbers,
text, datetime) Calc will convert the raw text into a more appropriate
data type. Also, once checkbox is selected, Calc will remember the
setting for subsequent imports.

This may be easier than cut/paste special.
Jay Lozier

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: 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.