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: [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 > > Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- 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
