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

I ended up using this option for this time, create a column using the VALUE function, then copy and 
paste special numbers only.

For future I will look at other options. This is something that will be done a lot for the next 
year or so.

I am in the process of converting my sheep records from a LO Spreadsheet system into an SQLite 
database along with writing my own flock management program, LambTracker. During the 
writing/conversion I am maintaining both systems because I have a requirement to keep records for 
the federal government and I can't risk a bug in my SW screwing up the database or making my 
records/reports inaccurate. So while I am still in the writing/debugging (estimated to be the next 
year for initial work, then another year to fine tune) I need to maintain both systems. 

I will look at other options that will automate the process for the future but I needed to get 
something done quickly as I had to report the results this week. 

On Nov 10, 2013, at 6:37 PM, Brian Barker wrote:

> At 14:57 10/11/2013 -0700, Oogie McGuire 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.
> >
> > 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!
>
> There are various ways to do this, and you may want to experiment in order to discover what suits 
> your work flow.
>
> The VALUE() function?  Yes, you can use that:
> o In a spare column, row, or range (as appropriate), or even on another sheet, enter =VALUE(Xn) - 
> where Xn is the start of the range.
> o Fill the formula down the column, along the row, or through the range.  You now have a copy of 
> your data - but as numbers, not text.
> o If desired, copy the numeric values and paste them back over the originals, but using Edit | 
> Paste Special... (or right-click | Paste Special... or Ctrl+Shift+V) instead of ordinary Paste.
> o In the Paste Special dialogue, remove the tick from "Paste all" if necessary and ensure that 
> Numbers and Formats are ticked but Formulas is not ticked.
> o You can now delete the temporary values or delete their rows or columns if you wish.
>
> No, changing the format of the cells will not help: a cell's format affects the way its value is 
> displayed and the way inserted values are handled, but not the actual contents of the cell.
>
> Another easy way is via a comma-separated-value file.  Save the sheet with your rogue text values 
> using File | Save As... and selecting "Text CSV (.csv)" for "Save as type:".  Now open the 
> resulting (temporary, scratch) CSV file in LibreOffice and you will have numeric values that you 
> can copy where you need them.  Yes - as has already been suggested - you could alternatively 
> import the CSV file as an additional sheet in your existing spreadsheet document.
>
> I trust this helps.
>
> Brian Barker
>
>
> -- 
> 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

Eugenie (Oogie) McGuire 
Desert Weyr http://www.desertweyr.com/  
Paonia, CO USA


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