Re: [libreoffice-users] CALC convert text to numbers
On Sun, 17 Nov 2013 19:57:10 -0700 Oogie McGuire oog...@desertweyr.com wrote: On Nov 17, 2013, at 4:41 PM, Paul paulste...@afrihost.co.za wrote: Or if I know they are text (but there is ABSOLUTELY NO WAY TO SEE THAT IN LO) How would you see it in Excel? I recall the display in Excel being the same as in LO. The only difference between digits that are text and digits that are numbers is in the justification. In Excel you can look at the formatting and if the formatting is as text you can change it and the numbers are now considered numbers not text. You do have to look at the cell format but once you do it's obvious as either text or numbers. Well in LO you can't change the data type just by changing the formatting, but you should be able to see it! If you go to the format of a cell, and the format is text, then the values in the cell are text, but if the format is number, then the values are either numbers, or text, in which case they will be preceeded by an apostrophe in the input line. Although you say this isn't the case for you, this is how it *should* work. All the discussions about the leading apostrophe are great but I still can't see it in LO nor can I remove it. Cell by cell you can remove it, which is just the same as I remember Excel working. You can see it, and remove it, in the input line per cell. You can't see it in the actual cells, but you can't in Excel either, as I recall. Actually you can't see it in LO at all, in the input line or anywhere. At least not on my machine, a mac. I've just re-verified this for the umptenth time. I'm attaching a bit of a screen grab that shows the input line in LO of a file where the cell I am looking at is actually text. No apostrophe showing at all, no obvious way to tell the thing is actually text and no way to change it easily. As far as I can see from your screengrab, the number is left-aligned, meaning it is text. In that case it won't show an apostrophe, because the value is text in a cell formatted as text. It only shows an apostrophe if the value is text in a cell formatted as a number, because there is no need to mark text as text if it is in a cell formatted as text. This is (as I recall from some years ago) the same as Excel, I think. I wasn't able to get the text to columns to work on my spreadsheets so gave up on it. I haven't tried it again to see if it works. I'm also trying to get the extension mentioned a while back installed and running too. I haven't used that extension personaly, so I can't talk about that, but the Text to Columns really should work. For me, I just select some cells that have values that are text, but I want as numbers, then I select Text to Columns from the Data menu, and click Ok in the dialog that pops up. No need to do anything more. Note that the selected cells have to be in a continuous range in a single column. Also, for reference, my dialog settings are as follows: Separator options: Separated by: Tab Comma Text delimiter: Nothing else selected. I really hope this works for you. If not, let us know what is going wrong, and we'll see if we can't get it working. Perhaps seeing the screen grab will explain, there is no feedback to the user at all on the data type in LO. You mean apart from the justification? Are you sure the apostrophe is not showing when you have a text data value in a cell formatted as a number? And apart from these two indicators, what other indicators does Excel have (other than the fact that if you examine the format (in your version) it tells you what the data type is)? The source for most of my files that I have to add is from an SQLite Database. The fields that are in the table are defined to be of type either real or integer (depends on the characteristic I am looking at) in SQLite. Once I get the proper query done in the database I save the results as a .CSV file, move the file from the machine that is doing the field sheep evaluations to my main desktop mac where I have my main spreadsheep. I bring up the .CSV file and the main sheep spreadsheet file up at the same time and cut and paste as required. I can't replace whole columns, my subset from the database often does not include data for all sheep but only for those individuals who got evaluated that particular day. My spreadsheet file has one row per sheep and then hundreds of data points for that specific animal in the columns. Since often groups of sheep get the same thing done on the same date I may have many individuals with data related to a specific item like a vaccine or a wormer. If you can do Text to Columns on the .csv file before copying the data over, it would save you a lot of trouble, I think. We really need to get that working for you. On Mon, 18 Nov 2013 03:19:17 + Brian Barker b.m.bar...@btinternet.com wrote: In any case, just go to View | Value Highlighting (or Ctrl+F8). Text values will show in black and numbers (and
Re: [libreoffice-users] CALC convert text to numbers
Hi, What I am used to doing in Excel is copy everything in, then format the cells to be numbers and they are converted to numbers and my formula work. Really? I'm surprised. I didn't know Excel did this conversion just on formatting. I just tried in Excel 2013 and it´s simply not true. ;-) Changing the number format of a cell does not convert a text value into a number value. Stefan -- LibreOffice - Die Freiheit nehm' ich mir! -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
No it does NOT appear on the input line on my machine. I can't attach a screen shot to this message but I can send one to you if that would help. On Nov 17, 2013, at 8:19 PM, Brian Barker b.m.bar...@btinternet.com wrote: 'Cause it's not there! (It appears only in the Input Line.) Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
Hi :) MS Office 2013 is different from 2010 is different from 2007 is radically different from 2003. Formats change and sometimes cause problems. The layout of the ribbon-bar changes. The macro language changes quite a lot. What might have happened in one version sometimes vanishes from the next but may reappear in another. There is no-where that users can go to suggest changes or reversions. Regards from Tom :) On 18 November 2013 08:13, Stefan Weigel stefan.wei...@bildungskreis.org wrote: Hi, What I am used to doing in Excel is copy everything in, then format the cells to be numbers and they are converted to numbers and my formula work. Really? I'm surprised. I didn't know Excel did this conversion just on formatting. I just tried in Excel 2013 and it´s simply not true. ;-) Changing the number format of a cell does not convert a text value into a number value. Stefan -- LibreOffice - Die Freiheit nehm' ich mir! -- To unsubscribe e-mail to: users+unsubscr...@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 -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
My Excel was from 2000. It worked there, I have never upgraded it past that version. On Nov 18, 2013, at 1:13 AM, Stefan Weigel stefan.wei...@bildungskreis.org wrote: I just tried in Excel 2013 and it´s simply not true. ;-) Changing the number format of a cell does not convert a text value into a number value. Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
Yes and no, Yes everything changes from version to version in Microsoft Office. As I mentioned in another message the version I have is from 2000. No in that you can submit feedback and changes to MS Office, and in fact from rev 1 (I was an Office User from the very beginning) to the last version I used (2000) I did that a lot and in fact many of the suggestions did find their way into the later versions. Given my own experience with OpenOffice and now Libre Office it's no easier to suggest changes here than it was for Microsoft Office. They may get listened to a bit more in LO, no guarantee though. On Nov 18, 2013, at 6:42 AM, Tom Davies tomdavie...@yahoo.co.uk wrote: Hi :) MS Office 2013 is different from 2010 is different from 2007 is radically different from 2003. Formats change and sometimes cause problems. The layout of the ribbon-bar changes. The macro language changes quite a lot. What might have happened in one version sometimes vanishes from the next but may reappear in another. There is no-where that users can go to suggest changes or reversions. Regards from Tom :) Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@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
Fwd: [libreoffice-users] CALC convert text to numbers
Forgot to include the list on my answer Begin forwarded message: From: Oogie McGuire oog...@desertweyr.com Subject: Re: [libreoffice-users] CALC convert text to numbers Date: November 18, 2013 10:23:07 AM MST To: Tom Davies tomc...@gmail.com It's been over 13 years ago, At that time I believe it was just send feedback. I don't really remember. I do know that I kept a log of the things I asked for over the years as it was part of my job to request additions/modifications from vendors of our basic tools. I also know that over time Microsoft corrected /added a number of those requests to later versions. I have no clue whether it was any specific user request or we just happened to request things MS planned to do anyway. On Nov 18, 2013, at 10:05 AM, Tom Davies tomc...@gmail.com wrote: Where do you post to? Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
Hi :) Where do you post to? I've never known anyone else say that MS Office changed direction on anything in order to accommodate a user's suggestion so this is a bit of a first. Usually users are just told they can't be having the problem or get told to upgrade. MS don't claim to listen to individual users so on the odd occasion they do seem to have listened they get applauded for it. They couldn't hope to be as responsive as LO. LO is held to a much higher standard in terms of responsiveness. Regards from Tom :) On 18 November 2013 16:36, Oogie McGuire oog...@desertweyr.com wrote: Yes and no, Yes everything changes from version to version in Microsoft Office. As I mentioned in another message the version I have is from 2000. No in that you can submit feedback and changes to MS Office, and in fact from rev 1 (I was an Office User from the very beginning) to the last version I used (2000) I did that a lot and in fact many of the suggestions did find their way into the later versions. Given my own experience with OpenOffice and now Libre Office it's no easier to suggest changes here than it was for Microsoft Office. They may get listened to a bit more in LO, no guarantee though. On Nov 18, 2013, at 6:42 AM, Tom Davies tomdavie...@yahoo.co.uk wrote: Hi :) MS Office 2013 is different from 2010 is different from 2007 is radically different from 2003. Formats change and sometimes cause problems. The layout of the ribbon-bar changes. The macro language changes quite a lot. What might have happened in one version sometimes vanishes from the next but may reappear in another. There is no-where that users can go to suggest changes or reversions. Regards from Tom :) Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
Hi :) Thanks :) imo It's unreasonable to expect anyone to track these sorts of things or even if they do track it then sharing that data is tricky so i wasn't really expecting an answer at all. I liked your answer and it is good to hear they are not quite as unresponsive as i've always heard. Thanks and regards from Tom :) On 18 November 2013 17:37, Oogie McGuire oog...@desertweyr.com wrote: Forgot to include the list on my answer Begin forwarded message: From: Oogie McGuire oog...@desertweyr.com Subject: Re: [libreoffice-users] CALC convert text to numbers Date: November 18, 2013 10:23:07 AM MST To: Tom Davies tomc...@gmail.com It's been over 13 years ago, At that time I believe it was just send feedback. I don't really remember. I do know that I kept a log of the things I asked for over the years as it was part of my job to request additions/modifications from vendors of our basic tools. I also know that over time Microsoft corrected /added a number of those requests to later versions. I have no clue whether it was any specific user request or we just happened to request things MS planned to do anyway. On Nov 18, 2013, at 10:05 AM, Tom Davies tomc...@gmail.com wrote: Where do you post to? Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@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 -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
I'm with Charles on this one. I was the OP on the problem. Nearly all of my issues are because I have my LO spreadsheet and then a CSV file. If I open the CSV file in LO it looks fine. I can't just import it into my existing spreadsheet because the data in it need to go into small subsets of my big spreadsheet. So I typically copy and paste groups of cells as required. What I am used to doing in Excel is copy everything in, then format the cells to be numbers and they are converted to numbers and my formula work. Or if I know they are text (but there is ABSOLUTELY NO WAY TO SEE THAT IN LO) I can do a paste special and that also works in excel and prevents the additional step of formatting the cells back to numbers. All the discussions about the leading apostrophe are great but I still can't see it in LO nor can I remove it. Yes, I got around the problem this time by creating a dummy column as described many messages ago, but the fact remains that it is neither easy to change nor easy to see when numbers are formatted as text in LO and coming from MsOffice this causes lots of problems. On Nov 17, 2013, at 12:49 PM, Charles Smith c...@chucsmith.org wrote: Hello, I can only speak for myself, but numbers get formatted as text in two ways: 1. Import of a tab delimited or csv file. 2. Spreadsheets sent to me from other users who have imported such files. Usually I format the import to avoid the problem, but if I just double click a csv file, it opens with the numbers formatted as text. Visually this is not a problem. But if I then decide I need to edit the sheet I either have to reimport it or reformat the numbers. Hope this explains how it happens to me. Charles Sent from my iPod Touch On Nov 17, 2013, at 12:16 PM, James E Lang jim+...@lang.hm wrote: I've been following this debate with great interest. One big question comes to mind: Why would someone use the apostrophe construct in the first place if he intended to perform arithmetic calculations using the cell content? I understand ending up with a text string rather than a number by forgetting to use VALUE() on a substring in a formula but even that seems to fall into the category of a cockpit error rather than an aircraft design flaw as is being implied on one side of this debate. -- Jim -- To unsubscribe e-mail to: users+unsubscr...@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 -- To unsubscribe e-mail to: users+unsubscr...@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+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
Hi. I have sort of been following this in the background. Is this a recent issue in LO. I frequently open csv files with numbers and copy and paste into other sheets and the numbers work in formulae. Steve On 2013-11-18 09:54, Oogie McGuire wrote: I'm with Charles on this one. I was the OP on the problem. Nearly all of my issues are because I have my LO spreadsheet and then a CSV file. If I open the CSV file in LO it looks fine. I can't just import it into my existing spreadsheet because the data in it need to go into small subsets of my big spreadsheet. So I typically copy and paste groups of cells as required. What I am used to doing in Excel is copy everything in, then format the cells to be numbers and they are converted to numbers and my formula work. Or if I know they are text (but there is ABSOLUTELY NO WAY TO SEE THAT IN LO) I can do a paste special and that also works in excel and prevents the additional step of formatting the cells back to numbers. All the discussions about the leading apostrophe are great but I still can't see it in LO nor can I remove it. Yes, I got around the problem this time by creating a dummy column as described many messages ago, but the fact remains that it is neither easy to change nor easy to see when numbers are formatted as text in LO and coming from MsOffice this causes lots of problems. On Nov 17, 2013, at 12:49 PM, Charles Smith c...@chucsmith.org wrote: Hello, I can only speak for myself, but numbers get formatted as text in two ways: 1. Import of a tab delimited or csv file. 2. Spreadsheets sent to me from other users who have imported such files. Usually I format the import to avoid the problem, but if I just double click a csv file, it opens with the numbers formatted as text. Visually this is not a problem. But if I then decide I need to edit the sheet I either have to reimport it or reformat the numbers. Hope this explains how it happens to me. Charles Sent from my iPod Touch On Nov 17, 2013, at 12:16 PM, James E Lang jim+...@lang.hm wrote: I've been following this debate with great interest. One big question comes to mind: Why would someone use the apostrophe construct in the first place if he intended to perform arithmetic calculations using the cell content? I understand ending up with a text string rather than a number by forgetting to use VALUE() on a substring in a formula but even that seems to fall into the category of a cockpit error rather than an aircraft design flaw as is being implied on one side of this debate. -- Jim -- To unsubscribe e-mail to: users+unsubscr...@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 -- To unsubscribe e-mail to: users+unsubscr...@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+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
On Sun, 17 Nov 2013 13:54:59 -0700 Oogie McGuire oog...@desertweyr.com wrote: What I am used to doing in Excel is copy everything in, then format the cells to be numbers and they are converted to numbers and my formula work. Really? I'm surprised. I didn't know Excel did this conversion just on formatting. In LO you can use Text to Columns instead, surely? Or if I know they are text (but there is ABSOLUTELY NO WAY TO SEE THAT IN LO) How would you see it in Excel? I recall the display in Excel being the same as in LO. The only difference between digits that are text and digits that are numbers is in the justification. All the discussions about the leading apostrophe are great but I still can't see it in LO nor can I remove it. Cell by cell you can remove it, which is just the same as I remember Excel working. You can see it, and remove it, in the input line per cell. You can't see it in the actual cells, but you can't in Excel either, as I recall. Yes, I got around the problem this time by creating a dummy column as described many messages ago, but the fact remains that it is neither easy to change nor easy to see when numbers are formatted as text in LO and coming from MsOffice this causes lots of problems. Again, I'm puzzled by the implication that it is easier to see in Excel. I recall it being the same in Excel as in LO, you see it from the justification, unless that has been altered for the cells in question. As for changing it, it's pretty easy with the Text to Columns functionality; I really can't think of a more straightforward way. You're right that the paste and multiply trick doesn't work, but that still has to be done as an extra step doesn't it? You first paste the contents of the old sheet into the new sheet, then create a dummy cell holding a single 1, then copy this and paste it over the new cells, setting it to multiply. Surely this takes just as long, if not longer, than the Text to COlumns method? I agree the paste and multiply trick should work, but I'm just trying to understand why you say LO is harder to change the data type, and especially why you say it is harder to see the data type. Paul -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
At 13:54 17/11/2013 -0700, Oogie McGuire wrote: Or if I know they are text (but there is ABSOLUTELY NO WAY TO SEE THAT IN LO) ... Er, this is easy, in fact (and shouting doesn't change that!). Providing that you have not imposed any alignment or justification choice on your cells, text will be left-aligned and numbers (etc.) right-aligned. In any case, just go to View | Value Highlighting (or Ctrl+F8). Text values will show in black and numbers (and related quantities, e.g. currency and dates) in blue. These colours show despite existing text colour choices or formatting choices such as negative currency values. You can use this temporarily whilst you are massaging your values and return to normal view afterwards. All the discussions about the leading apostrophe are great but I still can't see it in LO nor can I remove it. 'Cause it's not there! (It appears only in the Input Line.) Yes, I got around the problem this time by creating a dummy column as described many messages ago, ... That was probably my suggestion, but someone else's Data | Text to Columns... technique is easier and therefore better. ... but the fact remains that it is neither easy to change nor easy to see when numbers are formatted as text in LO ... Hmm! Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
On Mon, 11 Nov 2013 16:26:28 -0700 Oogie McGuire oog...@desertweyr.com wrote: Try it, or I can send you a spreadsheet that fails to change the fonts and you can sew what I mean. I just tried it on a simple spreadsheet. Running LO 4.1.1.2 on Windows 7. I put some random text and numbers into a new spreadsheet, and changed some of the fonts and sizes, then clicked in the top left corner of the sheet to select all cells, changed font and size, and all cells changed to the new font and size. Then I changed font and size again, and again all cells changed to the new font and size. Seems to work fine for me. Perhaps put your example somewhere I can grab it, and I can test it if you want. On Nov 11, 2013, at 1:25 PM, Paul wrote: Just like i consider it a bug that if I select all cells in a spreadsheet and change the font or the font size I expect the contents of all cells to be modified to the new settings and it doesn't happen that way. This doesn't work for you? I'm surprised. I haven't checked, but I'm fairly sure it works as expected for me. Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
I just tried this extension. It's easy to use and seems to work very well. Is there a way to move its icon to a different place on the tool bar? Thanks, -Bill On 11/10/2013 5:08 PM, Joel Madero wrote: http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates That extension should do the trick - we're trying to incorporate it into the core code but so far you need the extension. All the best, Joel On 11/10/2013 01:57 PM, 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. 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 Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@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
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+unsubscr...@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+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
For me dealing with an extension, installing it, making sure it doesn't conflict with something else was more effort than creating a column, using Value() and then pasting special. What's a problem is that in Excel even though it also uses the leading ' to format text as numbers, if you change the format of a cell the conversions happen without any problems. I want that same behavior in Calc because to me it makes sense that the cell format should be the controlling factor for what type of data is in a given cell. On Nov 10, 2013, at 7:43 PM, Joel Madero wrote: Why is everyone straying away from the fact that there is a simple extension developed by Cor (one of our brilliant devs) which accomplishes all of this? Just curious if there's a benefit to doing these formula techniques instead of just pushing a button on a nice gui Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
Hi, Am 11.11.2013 18:17, schrieb Oogie McGuire: What's a problem is that in Excel even though it also uses the leading ' to format text as numbers, if you change the format of a cell the conversions happen without any problems. That´s not true. I just tried in Excel. The leading ' stays, no matter what format you would apply. to me it makes sense that the cell format should be the controlling factor for what type of data is in a given cell. Absolutely no! Format must never have an impact on values or data types. That´s a matter of logics. Cheers, Stefan -- LibreOffice - Die Freiheit nehm' ich mir! -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
Could this possibly be a 'feature' associated with a certain version of office\excel? Steve Gruspier Electrical Engineering On 11/11/2013 12:34 PM, Stefan Weigel wrote: Hi, Am 11.11.2013 18:17, schrieb Oogie McGuire: What's a problem is that in Excel even though it also uses the leading ' to format text as numbers, if you change the format of a cell the conversions happen without any problems. That´s not true. I just tried in Excel. The leading ' stays, no matter what format you would apply. Cheers, Stefan -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
Not in my version I just verified it. Had to go install Excel again to see but it works as I expect Macintosh Snow Leopard running MS Excel X for mac. On Nov 11, 2013, at 10:34 AM, Stefan Weigel wrote: That´s not true. I just tried in Excel. The leading ' stays, no matter what format you would apply. Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
On Mon, 11 Nov 2013 10:12:55 -0700 Oogie McGuire oog...@desertweyr.com wrote: What I would consider a bug is that changing the format of a cell doesn't change the data. And it shouldn't, it changes only the *format* of the data, not the data itself. I often get confused by this, expecting that when I change the format, my data has changed, but format is just about how the data is displayed, not about what the data is. Mostly this is absolutely correct, and format should *absolutely not* change the data. Most of the time when I have been expecting format to change the data is when I have values that are text instead of numbers, and I format as numbers and then expect to have numbers in the cell. There should be an easy way to change from numbers to text and back (and the same for dates and any other data types), but format isn't the way. Just like i consider it a bug that if I select all cells in a spreadsheet and change the font or the font size I expect the contents of all cells to be modified to the new settings and it doesn't happen that way. This doesn't work for you? I'm surprised. I haven't checked, but I'm fairly sure it works as expected for me. Paul -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
Try it, or I can send you a spreadsheet that fails to change the fonts and you can sew what I mean. On Nov 11, 2013, at 1:25 PM, Paul wrote: This doesn't work for you? I'm surprised. I haven't checked, but I'm fairly sure it works as expected for me. Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@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
RE: [libreoffice-users] CALC convert text to numbers
Spreadsheets use the MVC (model view controller) paradigm. That means that the model (how the data are actually stored) and how you view the data are separated. You can take a number like 4.5 and view it as a date, a date and time, a real number, etc. You can easily compare dates because they are stored as numbers, not character strings like Monday, Nov. 11. Further, you can easily send your spreadsheet to someone who only knows some language you have never heard of and s/he can open it and display and compare the dates in whatever language s/he has set. The best way to see if a cell contains a number, text, or a formula is to use View - Value Highlighting (F8). (Does Excel even have this feature? If so, it must hidden in the ribbon somewhere.) A zero as text has the ASCII value 48; as a number, the value is 0, so text and numbers are not equal. OpenOffice used to generate errors if one improperly tried to add text and a number, for example. Along the way, that behavior was modified to emulate Excel. (I prefered the old way along with the fact that either OOo or gnumeric or both used to evaluate -1^2 correctly--the mathematical answer is -1, not 1.) I just checked using Excel 2010, if you change the format (the view) of the cell, the underlying representation (the model) does not change. 1. Type '123 in a cell, say A1 2. Right click and choose Format Cells, then Format as a number. (That is, change General to Number.) The entry is still text. You can confirm because =sum(A1) yields 0. Note: =A1+0 yields 123. (Also the text is still left justified.) That is, there is no conversion. Best regards, David Gast From: Oogie McGuire [oog...@desertweyr.com] Sent: Monday, November 11, 2013 09:17 To: Joel Madero Cc: Brian Barker; users@global.libreoffice.org Subject: Re: [libreoffice-users] CALC convert text to numbers For me dealing with an extension, installing it, making sure it doesn't conflict with something else was more effort than creating a column, using Value() and then pasting special. What's a problem is that in Excel even though it also uses the leading ' to format text as numbers, if you change the format of a cell the conversions happen without any problems. I want that same behavior in Calc because to me it makes sense that the cell format should be the controlling factor for what type of data is in a given cell. On Nov 10, 2013, at 7:43 PM, Joel Madero wrote: Why is everyone straying away from the fact that there is a simple extension developed by Cor (one of our brilliant devs) which accomplishes all of this? Just curious if there's a benefit to doing these formula techniques instead of just pushing a button on a nice gui Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@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 -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
At 05:51 11/11/2013 -0500, William Drago wrote: I just tried this extension. It's easy to use and seems to work very well. Is there a way to move its icon to a different place on the tool bar? Yes. o Click the down-arrow at the right end of the relevant toolbar and select Customize Toolbar... . o Under Toolbar Content, scroll down and select the relevant icon. o Use the up and down arrows at the right of the Commands panel to move the icon to where you prefer it. o OK. I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
[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 Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates That extension should do the trick - we're trying to incorporate it into the core code but so far you need the extension. All the best, Joel On 11/10/2013 01:57 PM, 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. 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 Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@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
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+unsubscr...@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
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
jsloz...@gmail.com
--
To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
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+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
On Mon, 11 Nov 2013 02:56:51 +0200 Ady ady...@hotmail.com wrote: 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. As far as I understand it, the hidden initial quotation mark is what marks the contents of the cell as text. This is the same in MS Excel, IIRC. So basically, this isn't a bug, but intended behaviour, to give you a way to specify that a number should be interpreted as text and not as a number. For example, if you enter 0283, the leading zero will always be stripped, because it is interpreted as a number and the leading zero is superfluous, but if you enter '0283, then this means you have entered a text string and the leading zero is kept. The format of the cell doesn't change this behavior, it only changes the *display* of the contents, not the interpretation of the contents. At least, as I understand it. Paul -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
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. -- Yes, as I said, if there is still a need to 'import', that's simple enough. But if the data is already in a spreadsheet, there is a well-known paste special simple procedure. It works in other (more than one) spreadsheet tools and usually is fast enough. The more important matter is that LO Calc is making this simple conversion more complicated than necessary. I wonder if this should be reported as a bug / enhancement, so the behavior is changed / improved. Any comment from Calc developers would be really appreciated. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
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! Oh, here's another way: o Ensure that the cells with your rogue values are actually formatted as numbers. (No, that won't solve your problem, but bear with me.) o Select the cells. o Search for .* and replace with - but click More Options and tick Current selection only and Regular expressions. The text values are reinserted in their cells, but are also reinterpreted - as numbers. I trust this (also) helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
At 02:56 11/11/2013 +0200, Ady Noname wrote:
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).
No, the reason this fails is because you are trying to multiply text
by a number. If this worked, the original problem would not exist:
the user would be able to involve his text values in mathematical
formulae directly. And it's wrong to think of that leading single
quotation mark as actually being in the cell. Preceding a numeric
value by a quotation mark is how you indicate in typing that you want
the value to remain as text and that you do not want the value
interpreted as a number. It's also the way that values shown in the
Input Line that might appear to be numbers are indicated actually to
be text. But there is no quote in the cell.
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).
Indeed: it won't find it because it's not there!
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.
Aaargh! The quotation mark is not added to the cell, but is used to
indicate that you have preserved the value as text, not had it
interpreted as a number. That's most definitely a feature, not a
bug! There are genuine uses for this: if you live in Newark, New
Jersey, USA, your ZIP code may be the five-character text string
07102. But the postal system will not like your misrepresenting this
as 7102 - the number seven thousand, one hundred and two.
Brian Barker
--
To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
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. As far as I understand it, the hidden initial quotation mark is what marks the contents of the cell as text. This is the same in MS Excel, IIRC. So basically, this isn't a bug, but intended behaviour, to give you a way to specify that a number should be interpreted as text and not as a number. For example, if you enter 0283, the leading zero will always be stripped, because it is interpreted as a number and the leading zero is superfluous, but if you enter '0283, then this means you have entered a text string and the leading zero is kept. The format of the cell doesn't change this behavior, it only changes the *display* of the contents, not the interpretation of the contents. At least, as I understand it. Paul Well, yes, but no :). In Excel and other spreadsheet tools, you could: 1_ In an auxiliary non-formatted cell, insert the number 1. 2_ Copy that auxiliary cell. 3_ Select the cells with numbers that are currently formatted as 'text' that you want to convert. 4_ Paste special (all), multiply. 5_ Delete the auxiliary cell. If the desired format is not just a 'general' number, then you can format the auxiliary cell before copying it. This simple procedure cannot be used in Calc, just because this hidden single quotation mark. This procedure is successful in other spreadsheet tools that use the single quotation mark too. So, why not in Calc? BTW, the quotation mark is not necessary for all cells containing numbers formatted as text, but that's off-topic here. My point is still relevant: the aforementioned procedure should be plausible in Calc, just as it is in other spreadsheet tools. There are workarounds, like using the 'VALUE' function in an auxiliary column and then copy+paste back; yet I still wonder about this hidden (annoying) single quotation mark. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
On Mon, 2013-11-11 at 03:40 +0200, Paul wrote: On Mon, 11 Nov 2013 02:56:51 +0200 Ady ady...@hotmail.com wrote: 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. As far as I understand it, the hidden initial quotation mark is what marks the contents of the cell as text. This is the same in MS Excel, IIRC. So basically, this isn't a bug, but intended behaviour, to give you a way to specify that a number should be interpreted as text and not as a number. For example, if you enter 0283, the leading zero will always be stripped, because it is interpreted as a number and the leading zero is superfluous, but if you enter '0283, then this means you have entered a text string and the leading zero is kept. The format of the cell doesn't change this behavior, it only changes the *display* of the contents, not the interpretation of the contents. At least, as I understand it. Paul US zip codes (postal codes) may have a leading zero. Why not set a cell to the formula =right(len(CELL)-1) which returns the all the characters except for the first. -- Jay Lozier jsloz...@gmail.com -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
At 21:17 10/11/2013 -0500, Jay Lozier wrote: Why not set a cell to the formula =right(len(CELL)-1) which returns the all the characters except for the first. That's =RIGHT(Xn;LEN(Xn)-1) - but that won't strip the single quote mark because it's *not there*! The simpler formula =RIGHT(Xn;LEN(Xn)) would work, but that's just a complicated way of saying =VALUE(Xn). Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
On 11/10/2013 06:32 PM, Brian Barker wrote: At 21:17 10/11/2013 -0500, Jay Lozier wrote: Why not set a cell to the formula =right(len(CELL)-1) which returns the all the characters except for the first. That's =RIGHT(Xn;LEN(Xn)-1) - but that won't strip the single quote mark because it's *not there*! The simpler formula =RIGHT(Xn;LEN(Xn)) would work, but that's just a complicated way of saying =VALUE(Xn). Brian Barker Why is everyone straying away from the fact that there is a simple extension developed by Cor (one of our brilliant devs) which accomplishes all of this? Just curious if there's a benefit to doing these formula techniques instead of just pushing a button on a nice gui All the best, Joel -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
Why not set a cell to the formula =right(len(CELL)-1) which returns the all the characters except for the first. -- No, that won't work. You probably meant: =RIGHT(A1,LEN(A1)-1) where A1 is the 'text' cell. But that one will fail too, because the initial single quotation mark is not really part of the content of the cell. (Note: it might work in some particular situation, but it is not a generic method.) The 'VALUE' method is a valid workaround, but it might be a heavy method, depending on the amount of data. I would prefer the 'paste special'+multiply by 1 method to work. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
At 18:43 10/11/2013 -0800, Joel Madero wrote: Why is everyone straying away from the fact that there is a simple extension developed by Cor (one of our brilliant devs) which accomplishes all of this? Just curious if there's a benefit to doing these formula techniques instead of just pushing a button on a nice gui Oh, that's easy: 1. List subscribers will have read earlier suggestions and are intelligent enough to ignore later ones if they prefer. 2. Different methods are appropriate in different circumstances - and subscribers other than the original questioner may find them helpful to their different needs. 3. It's important that questionable statements made on a public list read by many (and indeed archived) are challenged. (Anyone is welcome to trash this message if desired!) Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
On 11/10/2013 07:05 PM, Brian Barker wrote: At 18:43 10/11/2013 -0800, Joel Madero wrote: Why is everyone straying away from the fact that there is a simple extension developed by Cor (one of our brilliant devs) which accomplishes all of this? Just curious if there's a benefit to doing these formula techniques instead of just pushing a button on a nice gui Oh, that's easy: 1. List subscribers will have read earlier suggestions and are intelligent enough to ignore later ones if they prefer. 2. Different methods are appropriate in different circumstances - and subscribers other than the original questioner may find them helpful to their different needs. 3. It's important that questionable statements made on a public list read by many (and indeed archived) are challenged. (Anyone is welcome to trash this message if desired!) Brian Barker :-D Fair enough. I was actually just curious if there is a benefit to the formulas over the text to number as I always use it but would change if there was a benefit to some other method :-D Thanks for explaining! All the best, Joel -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
Why is everyone straying away from the fact that there is a simple extension developed by Cor (one of our brilliant devs) which accomplishes all of this? Just curious if there's a benefit to doing these formula techniques instead of just pushing a button on a nice gui I could ask you the same type of question regarding paste special + multiply by 1 method. A user might not know of the extension, or about any extension for that matter. A user might not use extensions, ever. Why would anyone need to search, install and use an extension, when other spreadsheet tools can apply a well-known multiply by 1 method? In other words, if very simple spreadsheet tools (not just Excel) can use a very simple and well-known method, perhaps instead of thinking of how to incorporate the CT2N extension into the core in Calc, the same time could be invested to make Calc work as expected with paste special? To be clear, I'm not complaining, just posting my thoughts. Regards, Ady. -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
On 11/10/2013 07:10 PM, Ady wrote: Why is everyone straying away from the fact that there is a simple extension developed by Cor (one of our brilliant devs) which accomplishes all of this? Just curious if there's a benefit to doing these formula techniques instead of just pushing a button on a nice gui I could ask you the same type of question regarding paste special + multiply by 1 method. A user might not know of the extension, or about any extension for that matter. A user might not use extensions, ever. Why would anyone need to search, install and use an extension, when other spreadsheet tools can apply a well-known multiply by 1 method? In other words, if very simple spreadsheet tools (not just Excel) can use a very simple and well-known method, perhaps instead of thinking of how to incorporate the CT2N extension into the core in Calc, the same time could be invested to make Calc work as expected with paste special? To be clear, I'm not complaining, just posting my thoughts. Regards, Ady. Thoughts are always appreciated in our community :-D Best, Joel -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
Le 10/11/2013 22:57, Oogie McGuire a écrit :
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.
This will do quite easily:
Call the menu Data / Numbers as text (freely translated from my FR
install) then just click OK in the window that opens without changing
anything. This should do the trick.
Another way I often use when the import is made on a regular basis, is
to create what I call an exploitation sheet.
You create a new spreadsheet and insert data there from the spreadsheet
you got from the import. The formulas will then look like
='file:///home/jf/Documents/Imports/MyImport.ods'#$'Sheet1'.A2
Then copy down and right and you're set.
What is interesting is that you may add some data handling when linking
the cells: you may have
=CNUM('file:///home/jf/Documents/Imports/MyImport.ods'#$'Sheet1'.A2)
which would automagically convert the text data into numerical data
without having to bother each and every time a new import comes.
You may also ignore any superfluous column, and so on.
For the ease of use, make sure to have both spreadsheets in the same
directory.
Note that:
-- this works only if the imported data is consistent (same columns at
the same place) and the data file must be uniformly named.
-- the imported data must be in a real file on disk.
HTH,
--
Jean-Francois Nifenecker, Bordeaux
--
To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
ooops! forgot to specify the first step: 1. select the column to set as numbers first. Le 11/11/2013 07:03, Jean-Francois Nifenecker a écrit : 2. do that: Call the menu Data / Numbers as text (freely translated from my FR install) then just click OK in the window that opens without changing anything. This should do the trick. -- Jean-Francois Nifenecker, Bordeaux -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
At 07:03 11/11/2013 +0100, Jean-Francois Nifenecker wrote: Call the menu Data / Numbers as text (freely translated from my FR install) ... In English, that's Data | Text to Columns... . Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] CALC convert text to numbers
Le 11/11/2013 07:44, Brian Barker a écrit : At 07:03 11/11/2013 +0100, Jean-Francois Nifenecker wrote: Call the menu Data / Numbers as text (freely translated from my FR install) ... In English, that's Data | Text to Columns... . thanks! -- Jean-Francois Nifenecker, Bordeaux -- To unsubscribe e-mail to: users+unsubscr...@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
