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 [email protected] -- 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
