Re: [libreoffice-users] CALC convert text to numbers

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