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

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. 
> >
> > 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.
Hi,

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
jslozier@gmail.com


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