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!
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
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.
To unsubscribe e-mail to: email@example.com
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