> 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.
>
> https://help.libreoffice.org/Calc/Converting_Text_to_Numbers 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.
Regards,
Ady.
--
To unsubscribe e-mail to: [email protected]
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
