[libreoffice-users] Re: CALC convert text to numbers
El 2013-11-14 05:07 p.m., mariosv escribió: I have forgot in my previous post, another option: First copying the text to convert [Ctrl+c]. From the right-arrow in the icon. selecting unformatted text, we get the same option as importing csv files. http://nabble.documentfoundation.org/file/n4083240/Captura.png Miguel Ángel. -- View this message in context: http://nabble.documentfoundation.org/CALC-convert-text-to-numbers-tp4082413p4083240.html Sent from the Users mailing list archive at Nabble.com. That's a nice solution. -- 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] Re: CALC convert text to numbers
The only way I have to know that the numbers are in reality text, are that they are originally left aligned. Numbers are right aligned. In other matter, I get the ' when I apply a number format to a cell previously formatted as text. If I edit it (F2) I can reach the ' and edit it by hand. This is really slow and not practical. El 2013-11-17 02:54 p.m., Oogie McGuire escribió: 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] Re: CALC convert text to numbers
On 11/15/13 4:11 PM, Paul wrote: the behaviour is correct, and consistant with other spreadsheet programs. Sorry, no. The behavior may be correct, but it is not completely consistent with other spreadsheet programs. At least it is not completely consistent with how Excel implements this. In particular, I still am unable to simply paste special a cell containing the number 1 onto a cell (or range of cells) containing a text representation of a number and, using the multiply choice, have that cell's contents converted to a number from text. This is what is done widely among Excel users. Granted the procedure is not intuitive, but it is widely known and used. You may argue that it is behavior that a spreadsheet should not allow and I could be persuaded to agree, however, Libreoffice's behavior here is not consistent with other spreadsheet programs in regard to this feature. In my opinion, Libreoffice does not have to do everything the way Excel does it, but when it differs, we should be clear that it differs. Lastly, this thread has been very valuable to me in that I now have several alternate methods of handling columns of numbers which are wrongly formatted as text. I deeply appreciate this resource. Charles -- 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] Re: CALC convert text to numbers
On Sun, 17 Nov 2013 11:33:36 -0600 Charles Smith c...@chucsmith.org wrote: On 11/15/13 4:11 PM, Paul wrote: the behaviour is correct, and consistant with other spreadsheet programs. Sorry, no. The behavior may be correct, but it is not completely consistent with other spreadsheet programs. At least it is not completely consistent with how Excel implements this. In particular, I still am unable to simply paste special a cell containing the number 1 onto a cell (or range of cells) containing a text representation of a number and, using the multiply choice, have that cell's contents converted to a number from text. This is what is done widely among Excel users. You are confusing two issues here. The first regards the apostrophe marking cell contents as text when they appear to be numbers. The second is the issue of allowing pasting and multiplying over cells containing a text representation of a number. You are correct in that the second issue is not consistent with Excel. However, I was speaking specifically of the first issue when I said that the behaviour is correct and consistant. In this discussion a few people have confused the two, thinking that the apostrophe is somehow connected to, and possibly the cause of, the second issue. I'm trying to say that the apostrophe is a separate issue, and is *not* a bug, as some have suggested. The apostrophe shows correct behaviour, purely in and of itself. As a separate issue, Calc does not allow one to paste and multiply over cells that contain numbers as text, and that, completely aside from the issue of the apostrophe, is inconsistant with Excel. Just trying to clear up some of the confusion. 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] Re: CALC convert text to numbers
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
Re: [libreoffice-users] Re: CALC convert text to numbers
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
Re: [libreoffice-users] Re: CALC convert text to numbers
On Nov 17, 2013, at 12:01 PM, Paul paulste...@afrihost.co.za wrote: Just trying to clear up some of the confusion. Paul Thanks, Paul. That was helpful. Charles -- 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] Re: CALC convert text to numbers
At 01:30 15/11/2013 +0200, Ady Noname wrote: To be clear, the 'text' format of the cell shouldn't block the multiplication ... Text isn't (just) a format: it's a separate data type. You can format cells as number or text, but that doesn't change what's stored in them (except that it governs how input typing is interpreted). Your desire that text *data* should be a permissible argument in mathematical formulae is a preference, of course: others may prefer that it wasn't. ... since the *content* of the cell should be independent of the way it is being displayed in the cell. That's true, but you miss (or avoid) the point that in spreadsheets generally numbers and text are different data types of cell content. How you display the text string 1234 or how you display the number 1234 should indeed not affect the content of either cell, but it remains that the two data items are quite different. This is different from date, time, percent, currency, and so on, where what is stored is indeed just a number and the date-ness, time-ness, etc. exist only as formatting. You should be able to use that same content in whichever way you want, whether you display the cell with leading zeroes, decimal places, as text, or in yellow. In the situation we are discussing, it's simply not the same content: spreadsheets allow storage of text and numbers as separate concepts. In asking for text that looks like a number and the equivalent number itself to be treated interchangeably, you are arguing for weak typing. There are arguments too for strong typing. But surely in most information contexts, text and numbers at least are handled and typed differently? See http://en.wikipedia.org/wiki/Strong_and_weak_typing . All this is not to say, of course, that you cannot allow implicit conversions: it's OK to want a spreadsheet to interpret some text as the equivalent number when the value is invoked in a mathematical context, but it is important to understand that such conversion is taking place. It's perhaps worth rehearsing here the real fundamental point (which may take some thinking about): that you can never enter numbers into a computer via a keyboard, since a keyboard handles only characters. If you type the three characters 1.2 into a cell, a (complicated) conversion from the character string to the single number they represent is performed for you - unless you choose to inhibit this by adding the leading apostrophe. 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] Re: CALC convert text to numbers
At 08:01 15/11/2013 +0100, Stefan Weigel wrote: Am 14.11.2013 22:39, schrieb Brian Barker: It's worth mentioning that this is a misunderstanding of what is happening. There is no apostrophe in the cell to remove. Yes there is! Sorry, but that's simply untrue - and you can easily show it. Type '1234 into a cell, so that you get the four-character text string 1234 in the cell (not the five-character string '1234). Now put =LEFT(Xn;1) in another cell - to extract just the first character. According to your theory, this formula should evaluate to just the apostrophe - or perhaps you think that the apostrophe would be suppressed and you would see nothing. But neither of these is that case: instead, you see the true first character, 1. It's because these cell contents are very different that the apostrophe is necessary as a warning. It's not a warning, it's an operator. It's somewhat like the = at the beginning of a formula. It's an operator when you include it in typing into a cell: it ensures that what you type is stored as text and not converted to a number. But it's surely not an operator when it appears in the Input Line. If it were an operator there, what operation do you think it would perform? 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] Re: CALC convert text to numbers
Am 15.11.2013 12:06, schrieb Brian Barker: Type '1234 into a cell, so that you get the four-character text string 1234 in the cell (not the five-character string '1234). I you do this, the content of the cell will be '1234 and the cell will display the text 1234 as a result. Now put =LEFT(Xn;1) in another cell - to extract just the first character. According to your theory, this formula should evaluate to just the apostrophe No. According to what the programme does, you will get the character 1, because it is the first character of the result in cell Xn. But it's surely not an operator when it appears in the Input Line. Yes it is. Just like an = tells the programme that the following has to be interpreted as a formula, the ' tells the programme that the following has to be interpreted as text. 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] Re: CALC convert text to numbers
Ok, I can't seem to refute this, because it would work exactly the same was as what I and Brian described works. The only difference would be in the internal representation of the cell contents, and I am not about to go diving into the code to check that out. Either way you think of it you will still see that it functions exactly the same, and the behaviour is correct, and consistant with other spreadsheet programs. Paul On Fri, 15 Nov 2013 22:28:40 +0100 Stefan Weigel stefan.wei...@bildungskreis.org wrote: Am 15.11.2013 12:06, schrieb Brian Barker: Type '1234 into a cell, so that you get the four-character text string 1234 in the cell (not the five-character string '1234). I you do this, the content of the cell will be '1234 and the cell will display the text 1234 as a result. Now put =LEFT(Xn;1) in another cell - to extract just the first character. According to your theory, this formula should evaluate to just the apostrophe No. According to what the programme does, you will get the character 1, because it is the first character of the result in cell Xn. But it's surely not an operator when it appears in the Input Line. Yes it is. Just like an = tells the programme that the following has to be interpreted as a formula, the ' tells the programme that the following has to be interpreted as text. 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] Re: CALC convert text to numbers
Hi Paul, Am 15.11.2013 23:11, schrieb Paul: the behaviour is correct, and consistant with other spreadsheet programs. I never doubted. :-) 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] Re: CALC convert text to numbers
On Thu, 14 Nov 2013 07:07:53 +0200 Ady ady...@hotmail.com wrote: Well, the procedure I mentioned involves copying one cell only, which means it is a faster method, as oppose to having to copy perhaps a lot of cells (e.g. a whole column; or having to add many new VALUE formulas; or having to add a new column and multiply by 1 each cell and then paste special...). I repeat the procedure that I posted before (which works correctly in other spreadsheet tools): 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. Hunh! Well, you live and learn. I didn't know you could do this. I see that this doesn't work, although using a formula of cell*1 does work. In other words, when using a formula, if a cell is given as input, and is text where a number is expected, an attempt is made to use VALUE on the cell. If copying and pasting, the attempt is not made. I'm not sure it is correct to ever do an implicit VALUE, but there is a good argument for making this copy paste method consistant with the behaviour of the function method. Although the Tools - Text to Columns... method in Calc is nice, the procedure I am describing is more flexible. I'm not sure why you say it is more flexible. Text to columns would be the correct way of doing this, and I can't right now think of a case where you would need your way. Can you give an example? -- 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] Re: CALC convert text to numbers
Although the Tools - Text to Columns... method in Calc is nice, the procedure I am describing is more flexible. I'm not sure why you say it is more flexible. Text to columns would be the correct way of doing this, and I can't right now think of a case where you would need your way. Can you give an example? -- First, evidently I meant to Data - Text to Columns... (not Tools...). My apologies. Currently in LO Calc 4.1.3.2, the Text to Column method will let you convert the text into one of a few specific number formats. If you want to convert to other formats (e.g. percentage, scientific, or some accounting type, or...), then paste special, multiply by 1 *should* let you do it. Also, if the 'text' cells contain additional characters such as thousand delimiters and the like, it might be possible to reduce the necessary steps to obtain the desired format conversion. But since this well-known method is currently unavailable in LO Calc, all this is wishful thinking only. I could give more examples, and of course that you could get the same result by using several steps. On one hand, this method is effective, efficient and well-known in several other spreadsheet programs, and for users that already know it, it seems at least strange that LO Calc doesn't support it. (BTW, I still think something fishy is happening with the single quotation mark in LO Calc.) On the other hand, there are (less efficient) alternatives to eventually get to the same final result in LO Calc. Seeing the current ratio of bug reports vs. bugs resolved, where in many of them there is no alternative available so to get the desired result using LO Calc, I am slightly reticent to actually report this as a bug (or as a potential enhancement, whichever the adequate term would be for this case). When other issues regarding paste (and/or paste special) get to be resolved, perhaps then this issue will get improved too. 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
[libreoffice-users] Re: CALC convert text to numbers
These solutions are far too complex especially if you have very large sheets. Why not use the extension CT2N? It is simple, very straightforward and you can decide ,whole sheet, or just parts. The ' is just removed. I understand it is to be included as standard, in which case there will be no need to install the extension. You can find it here: http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates Tink. -- View this message in context: http://nabble.documentfoundation.org/CALC-convert-text-to-numbers-tp4082413p4083203.html Sent from the Users mailing list archive at Nabble.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] Re: CALC convert text to numbers
Hi Ady, On Thu, 14 Nov 2013 15:45:33 +0200 Ady ady...@hotmail.com wrote: On one hand, this method is effective, efficient and well-known in several other spreadsheet programs, and for users that already know it, it seems at least strange that LO Calc doesn't support it. I'm still not sure it is correct behaviour, but you do have a strong argument. (BTW, I still think something fishy is happening with the single quotation mark in LO Calc.) The single quote is a red herring. It is correct behaviour, and the same as other spreadsheet software. It is the implicit conversion of the text to a value that differs. On the other hand, there are (less efficient) alternatives to eventually get to the same final result in LO Calc. And perhaps more correct ones. Correct in the sense that I'm not sure implicit conversions should be done on values. But it is a convenient shortcut... Seeing the current ratio of bug reports vs. bugs resolved, where in many of them there is no alternative available so to get the desired result using LO Calc, I am slightly reticent to actually report this as a bug (or as a potential enhancement, whichever the adequate term would be for this case). When other issues regarding paste (and/or paste special) get to be resolved, perhaps then this issue will get improved too. Yeah, there is something funny up with cut and paste, I'll post in a separate email, but I'm guessing the system needs an overhaul, which might mean this is looked at at the same time. 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] Re: CALC convert text to numbers
At 12:30 14/11/2013 -0800, J Taylor wrote: Why not use the extension CT2N? The ' is just removed. It's worth mentioning that this is a misunderstanding of what is happening. There is no apostrophe in the cell to remove. The apostrophe indicates (in the Input Line) that what may appear to be a number is actually text. After conversion it is no longer appropriate, so it doesn't appear. If you have 1234 as text, you have a four-byte character string. If you convert that (or the extension does) to a number, you get a (very probably) eight-byte floating point number, consisting of a mantissa and an exponent, each with its sign somehow encoded. There won't be a 1, a 2, a 3, or a 4 anywhere to be seen: only the composite number. That's very different, even though the formatted appearance in a spreadsheet cell could be identical. It's because these cell contents are very different that the apostrophe is necessary as a warning. 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] Re: CALC convert text to numbers
I have forgot in my previous post, another option: First copying the text to convert [Ctrl+c]. From the right-arrow in the icon. selecting unformatted text, we get the same option as importing csv files. http://nabble.documentfoundation.org/file/n4083240/Captura.png Miguel Ángel. -- View this message in context: http://nabble.documentfoundation.org/CALC-convert-text-to-numbers-tp4082413p4083240.html Sent from the Users mailing list archive at Nabble.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] Re: CALC convert text to numbers
These solutions are far too complex especially if you have very large sheets. Why not use the extension CT2N? It is simple, very straightforward and you can decide ,whole sheet, or just parts. The ' is just removed. I understand it is to be included as standard, in which case there will be no need to install the extension. You can find it here: http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates We are going in circles. The extension not always works (see bug reports), and the supposedly too complex solutions you are referring to take a few seconds and _less_ than 10 clicks. Regarding including the extension as standard, I hope the devs are using their valuable time to take care of features that _really_ have _no alternative_ in Calc. 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] Re: CALC convert text to numbers
Hi Ady, On Thu, 14 Nov 2013 15:45:33 +0200 Ady ady...@hotmail.com wrote: On one hand, this method is effective, efficient and well-known in several other spreadsheet programs, and for users that already know it, it seems at least strange that LO Calc doesn't support it. I'm still not sure it is correct behaviour, but you do have a strong argument. There is no correct behavior for this. The possibility to interpret / parse the content of a cell as a number (e.g. for usage in other cells) even when the original cell is formatted and displayed as 'text' is a feature, which is already present in other spreadsheet programs. A theoretical requirement to have to *always* use VALUE for any and all usage of those 'text' cells would trigger a natural enhancement request to make it easier. You can use VALUE if you think it is needed for some situation, but experience says that there are many simple cases where imposing its usage would negate common sense. There is a reason why such common feature has been present in spreadsheet programs for decades now. Perhaps thinking about it this way might help: 1_ In an auxiliary 'number' cell, insert the number 1; [ENTER]. 2_ Select that auxiliary cell. 3_ Copy that auxiliary cell. 4_ Select the cells with numbers that are currently formatted as 'text' that you want to convert. 5_ Paste special (all), multiply; OK. This paste special step is performing the following actions: 5.1_ It pastes first the cell format from the auxiliary cell, converting the selected cells from 'text' format to a new 'number' format; and, 5.2_ It multiplies the content of the selected cells by the content of the auxiliary cell (by 1 in this case); and, 5.3_ It adds all other characteristics of the auxiliary cell to the selected cells (e.g. comments). 6_ Delete the auxiliary cell. To be clear, the 'text' format of the cell shouldn't block the multiplication (in the above procedure or when using its content in other cells), since the *content* of the cell should be independent of the way it is being displayed in the cell. You should be able to use that same content in whichever way you want, whether you display the cell with leading zeroes, decimal places, as text, or in yellow. I don't know if LO Calc actually performs these actions in this way and order. I am just trying to explain why this feature makes sense (as it does in other spreadsheet tools). (BTW, I still think something fishy is happening with the single quotation mark in LO Calc.) The single quote is a red herring. It is correct behaviour, and the same as other spreadsheet software. It is the implicit conversion of the text to a value that differs. We are going in circles. The example that Denis Navas Vega already gave (with steps that you already followed) shows that the initial quotation mark is not necessary when you format the cell as 'text' before inserting its content. We all agree that the initial single quotation mark should not be part of the cell content; it's just an optional formatting aid to be used when fits the need. Yet, when in LO Calc you manually convert a cell (that you first formatted as 'text' and then inserted a pure number, without ') from 'text' to 'number' (with ctrl+1, number, general), the *previously nonexistent* initial quotation mark is *kept* (or rather *added*); it shouldn't!!! (a.k.a. BUG) That's the difference between LO Calc and other spreadsheet tools where the multiply by 1 conversion works as expected. On the other hand, there are (less efficient) alternatives to eventually get to the same final result in LO Calc. And perhaps more correct ones. Correct in the sense that I'm not sure implicit conversions should be done on values. But it is a convenient shortcut... See my prior rant about non-existent correct behavior vs common sense feature for this case. 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] Re: CALC convert text to numbers
Hi, Am 14.11.2013 22:39, schrieb Brian Barker: It's worth mentioning that this is a misunderstanding of what is happening. There is no apostrophe in the cell to remove. Yes there is! It's because these cell contents are very different that the apostrophe is necessary as a warning. It´s not a warning, it´s an operator. It's somewhat like the = at the beginning of a formula. 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] Re: CALC convert text to numbers
On Mon, 11 Nov 2013 20:54:54 -0600 Denis Navas Vega denis.na...@gmail.com wrote: Paul, Make this test. 1. Open a new worksheet. Format some cells, say from A5:A10 as text. Write some numbers on those cells. Ok 2. Copy those cells to other column, say to C5:C10 and format as number (format @). The '@' format is text, so I'm not sure what you want me to do, format as text, or format as number? You will see that the cells now shows an ' before the numbers. Yes, if I format as number, this is correct. 3. Write a formula in other cell, multiplying with 1, for instance, E5=C5*1 Ok Now check the cell and you will discover that you have a number. True enough, when I copy these cells, and paste special, pasting only text and numbers, not formulas or all, then I get numbers in the cells. Therefore, those numbers with ', that in reallity are text, can be multiplied by 1, to transform it to a number. As Brain explained to me, an implicit VALUE() must be done on the text when multiplying by 1. As a side note, I was not able to use the method of copy -- paste multiplying by one. How do you mean? When is the multiply done? If I copy the numbers from the cells with the formulas it works fine. So now I'm confused, if this *does* work, why was Ady complaining about it not working? -- 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] Re: CALC convert text to numbers
On Mon, 11 Nov 2013 20:54:54 -0600 Denis Navas Vega denis.na...@gmail.com wrote: Paul, Make this test. 1. Open a new worksheet. Format some cells, say from A5:A10 as text. Write some numbers on those cells. Ok 2. Copy those cells to other column, say to C5:C10 and format as number (format @). The '@' format is text, so I'm not sure what you want me to do, format as text, or format as number? You will see that the cells now shows an ' before the numbers. Yes, if I format as number, this is correct. 3. Write a formula in other cell, multiplying with 1, for instance, E5=C5*1 Ok Now check the cell and you will discover that you have a number. True enough, when I copy these cells, and paste special, pasting only text and numbers, not formulas or all, then I get numbers in the cells. Therefore, those numbers with ', that in reallity are text, can be multiplied by 1, to transform it to a number. As Brain explained to me, an implicit VALUE() must be done on the text when multiplying by 1. As a side note, I was not able to use the method of copy -- paste multiplying by one. How do you mean? When is the multiply done? If I copy the numbers from the cells with the formulas it works fine. So now I'm confused, if this *does* work, why was Ady complaining about it not working? -- Well, the procedure I mentioned involves copying one cell only, which means it is a faster method, as oppose to having to copy perhaps a lot of cells (e.g. a whole column; or having to add many new VALUE formulas; or having to add a new column and multiply by 1 each cell and then paste special...). I repeat the procedure that I posted before (which works correctly in other spreadsheet tools): 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, you could optionally format the auxiliary cell before copying it. Although the Tools - Text to Columns... method in Calc is nice, the procedure I am describing is more flexible. Unfortunately, it currently doesn't work in LibreOffice Calc 4.1.3.2. 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
[libreoffice-users] Re: CALC convert text to numbers
Brian If you are using a Mac 10.9 then you can move a Tool Bar icon simply by clicking on the gap to the right of the icon. When the object frame appears, drag the fame to where you want the icon, let go and the icon will move. I use CT2N when I copy and paste from a web site and it works, with one exception. If the number is prefixed by a currency sign, CT2N does not work. Cor very kindly gave me a solution. In your Macros you will find CT2N In the module Main Code, find function Check for Text in String. There you can find the line, 'new in 1.2.0: allow for negative numbers: -=45, (=40,)=41 Elseif j=45 AND i=1 Then' You can then insert before, or immediately after that line, 'Elseif j = 163 AND i = 1 Then ' allow for £' (Ignore the ' at the beginning and the end of the insert.) This also works, Many thanks, Cor. Tink. -- View this message in context: http://nabble.documentfoundation.org/CALC-convert-text-to-numbers-tp4082413p4082704.html Sent from the Users mailing list archive at Nabble.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
[libreoffice-users] Re: CALC convert text to numbers
El 2013-11-10 07:40 p.m., Paul escribió: 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 Paul, The problem is with the symbol ' It can't be searched and replaced. That's why Ady consider it a bug. The only solution, which I used yesterday, is multiply by one. More than a bug, we must consider it an incomplete implementation of the meaning of ' to accept figures as text. The operator should be reachable from Calc interface and not hidden. -- 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] Re: CALC convert text to numbers
On 11/11/2013 09:30 AM, Denis Navas Vega wrote: El 2013-11-10 07:40 p.m., Paul escribió: 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 Paul, The problem is with the symbol ' It can't be searched and replaced. That's why Ady consider it a bug. The only solution, which I used yesterday, is multiply by one. More than a bug, we must consider it an incomplete implementation of the meaning of ' to accept figures as text. The operator should be reachable from Calc interface and not hidden. As already said, this is all expected and happens in Excel also. There is some long technical reason why, I'm sure someone has written about it online if you google a bit :) 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] Re: CALC convert text to numbers
On Mon, 11 Nov 2013 11:30:11 -0600 Denis Navas Vega denis.na...@gmail.com wrote: The problem is with the symbol ' It can't be searched and replaced. That's why Ady consider it a bug. That's because it isn't actually there. It's just an indicator of the fact that the number is not a number, but a text string. You can't take it out to make the text string a number. You need to replace the text string with an actual number (that may *look* the same, but isn't). The only solution, which I used yesterday, is multiply by one. I still don't understand what that is supposed to do. It sounds like a kludge to me. The text string is a text string, I'm not sure why anybody would think multiplying a text string by 1 would give you a number. More than a bug, we must consider it an incomplete implementation of the meaning of ' to accept figures as text. The operator should be reachable from Calc interface and not hidden. Again, as explained before by myself and Brian, I think, it's *not* hidden, it just isn't part the the cell value. It is purely an indicator of what the cell value is, either text or numeric. It *is* reachable from the interface, in-as-much-as you can edit the cell contents and either put in a string marker, or remove a string marker. This is not a bug or incomplete feature. This is expected behaviour and works like other spreadsheet programs. 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
[libreoffice-users] Re: CALC convert text to numbers
1) When data is in the spreadsheet, is simple to do it in one column. Select the cell(s) to convert from text to number in one column. Menu/Data/Text to columns - Separating by (nothing selected) 2) With Search Replace Search for:.* Replace with: With regular expression selected in Other options. Miguel Ángel. -- View this message in context: http://nabble.documentfoundation.org/CALC-convert-text-to-numbers-tp4082413p4082651.html Sent from the Users mailing list archive at Nabble.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
[libreoffice-users] Re: CALC convert text to numbers
El 2013-11-11 01:46 p.m., Paul escribió: On Mon, 11 Nov 2013 11:30:11 -0600 Denis Navas Vega denis.na...@gmail.com wrote: The problem is with the symbol ' It can't be searched and replaced. That's why Ady consider it a bug. The only solution, which I used yesterday, is multiply by one. I still don't understand what that is supposed to do. It sounds like a kludge to me. The text string is a text string, I'm not sure why anybody would think multiplying a text string by 1 would give you a number. Paul Paul, Make this test. 1. Open a new worksheet. Format some cells, say from A5:A10 as text. Write some numbers on those cells. 2. Copy those cells to other column, say to C5:C10 and format as number (format @). You will see that the cells now shows an ' before the numbers. 3. Write a formula in other cell, multiplying with 1, for instance, E5=C5*1 Now check the cell and you will discover that you have a number. Therefore, those numbers with ', that in reallity are text, can be multiplied by 1, to transform it to a number. As a side note, I was not able to use the method of copy -- paste multiplying by one. -- 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] Re: CALC convert text to numbers
El 2013-11-11 04:00 p.m., David Gast escribió: 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 With Excel 2010 I made the following test, that contradicts your assertion. A) Write just: 123-- That's a number (just to compare). B) Write : '123-- That's text. Sum(cell) is equal to cero. C) Multiply: In another cell write a formula that references '123 address and multiply by 1. You get a number! Check it. -- 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
