Re: [libreoffice-users] pasting numbers into Calc (summary)
Folks, Thanks to everybody who replied. I still don't understand all of the problem, but I have found a solution that works for me. First in more detail what I'm doing and trying to do: In a Linux xterm, I get lines containing a special marker out of a file and onto the screen: [herbert@wardlaw10 NEB-test]$ grep @ scan4.xyz @ 180.00 -.2781258475E+03 @ -175.00 -.2781257976E+03 @ -170.00 -.2781256549E+03 @ -165.00 -.2781254333E+03 @ -160.00 -.2781251459E+03 I copy and paste that into a spreadsheet, the way you do it in Linux: left mouse button, middle mouse button. Of course it's text. When pasting (or afterwards in text to columns), I choose space as an additional delimiter, and merge delimiters. The spreadsheet splits the columns as expected, interprets the first number as a number and the second as text. I think it's the exponent that causes the problem, but this should be a valid number format. I doesn't help is to change the number format of that column (as somebody proposed). What does help is the =value(...), BUT somehow (from the pasting, I guess) some columns behind the newly filled ones end up being protected (whatever that is) and refuse to evaluate a formula, which took some figuring out. Anyway, after all this, I noticed a detect special numbers button in the paste menu, which seems to be the easiest solution for me. Thanks again Herbert -- Herbert Fruchtl Senior Scientific Computing Officer School of Chemistry, School of Mathematics and Statistics University of St Andrews -- The University of St Andrews is a charity registered in Scotland: No SC013532 -- For unsubscribe instructions e-mail to: users+h...@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] pasting numbers into Calc
Replace does work for these. Found this on the web many moons ago. How to convert numeric text to number by re-entering everything in one step. Select the cells in question. Apply any number format other than @ (text) menu:FindReplace... [More Options...] [X] Current Selection [X] Regular expression Search: .+ (a dot and a plus) Replace: [Replace All] On Thursday 11 Apr 2013 04:42:15 Brian Barker wrote: At 23:36 10/04/2013 +, Herbert Fruchtl wrote: This question seems to have been discussed before, but none of the replies I understand work for me. So what have you tried, in fact? If I copy a column-based piece of text, containing numbers in some columns, into LibreOffie Calc and apply the (hopefully) correct text-to-column delimiters, ... I'm not sure what you mean here. What are delimiters? Do you mean that you have, say, quote marks around your data? Is this perhaps a .CSV file? ... all columns (including the numbers) are interpreted as text. Then you presumably don't have this part correct, in fact! On closer inspection I find that at the start of each number there is a single quote (invisible in Calc, but I can delete it with backspace, and then the number is recognized as such). That leading single quote does not really exist in the cell; instead it appears in the Input Line to show that what appears to be a number is actually being stored in the cell as a text string - exactly what you don't want. You will also notice that, by default, the numbers-as-text are left aligned, whereas true numbers are right-aligned. Doing it manually cell by cell is the only way that works. No, there is a better method: read on! Paste special as number or as text doesn't work. You cannot Paste Special *as* anything; you can select only *what* you paste. If, as here, you have text, you can use Paste Special to select whether or not you paste it somewhere else, but not to change it to numbers. Search and replace doesn't work (or I do it wrong). That's right: the quote marks are not really there in the cell, so you cannot search for them. I have used OpenOffice and derivatives for years, but this one stumps me. Any ideas? Yes. But what works will depend on exactly what is going wrong for you. o Are the relevant destination cells already formatted as Text before you paste in the material? Ensure that they are formatted as General or Number instead. o When you paste the material in, do you see the Text Import panel? Can you tinker with the options there to achieve what you need? In particular, does Merge delimiters help? What are the column types indicated in the panel at the bottom of the panel? You can click each column and then adjust the column type using the drop-down menu. You probably need Standard, not Text. o In any case, you can solve the problem with your incorrect data. Suppose your data is in column A. In row 1 of a spare column, enter =VALUE(A1) and fill this formula down the column (and possibly across columns). The VALUE() function should take your text strings and convert them to true numbers. Now copy the data from the new column(s) and paste it back over the original data, but using Edit | Paste Special... (or Ctrl+Shift+V) instead of ordinary Paste. In the Paste Special dialogue, remove the tick from Paste all if necessary, and then ensure that Numbers is ticked but Formulas is not ticked. (Note that freezing the result of a formula into a plain value in this way is something you *can* do using Paste Special... .) I trust this helps. Brian Barker -- For unsubscribe instructions e-mail to: users+h...@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] pasting numbers into Calc
This question seems to have been discussed before, but none of the replies I understand work for me. Here it goes: If I copy a column-based piece of text, containing numbers in some columns, into LibreOffie Calc and apply the (hopefully) correct text-to-column delimiters, all columns (including the numbers) are interpreted as text. On closer inspection I find that at the start of each number there is a single quote (invisible in Calc, but I can delete it with backspace, and then the number is recognized as such). Doing it manually cell by cell is the only way that works. Paste special as number or as text doesn't work. Search and replace doesn't work (or I do it wrong). I don't think it's the locale; else the aforementioned manual delete of the quote wouldn't work. Writing macros is beyond my abilities (I don't know the language and I don't know where and how to enter them. If something that straight-forward requires complex programming, the software is broken). This is LibreOffice 3.4 on Linux Mint. The column-based text is just copied from an xterm with the mouse. I have used OpenOffice and derivatives for years, but this one stumps me. Any ideas? Herbert -- For unsubscribe instructions e-mail to: users+h...@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] pasting numbers into Calc
On 2013-04-11 11:36, Herbert Fruchtl wrote: This question seems to have been discussed before, but none of the replies I understand work for me. Here it goes: If I copy a column-based piece of text, containing numbers in some columns, into LibreOffie Calc and apply the (hopefully) correct text-to-column delimiters, all columns (including the numbers) are interpreted as text. On closer inspection I find that at the start of each number there is a single quote (invisible in Calc, but I can delete it with backspace, and then the number is recognized as such). Doing it manually cell by cell is the only way that works. Paste special as number or as text doesn't work. Search and replace doesn't work (or I do it wrong). I don't think it's the locale; else the aforementioned manual delete of the quote wouldn't work. Writing macros is beyond my abilities (I don't know the language and I don't know where and how to enter them. If something that straight-forward requires complex programming, the software is broken). This is LibreOffice 3.4 on Linux Mint. The column-based text is just copied from an xterm with the mouse. I have used OpenOffice and derivatives for years, but this one stumps me. Any ideas? Herbert Hi. What are you copying from. A calc sheet, a document, a web page. The ' in front explicitly formats as text. Steve -- For unsubscribe instructions e-mail to: users+h...@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] pasting numbers into Calc
On 04/10/2013 07:36 PM, Herbert Fruchtl wrote: This question seems to have been discussed before, but none of the replies I understand work for me. Here it goes: If I copy a column-based piece of text, containing numbers in some columns, into LibreOffie Calc and apply the (hopefully) correct text-to-column delimiters, all columns (including the numbers) are interpreted as text. On closer inspection I find that at the start of each number there is a single quote (invisible in Calc, but I can delete it with backspace, and then the number is recognized as such). Doing it manually cell by cell is the only way that works. Paste special as number or as text doesn't work. Search and replace doesn't work (or I do it wrong). I don't think it's the locale; else the aforementioned manual delete of the quote wouldn't work. Writing macros is beyond my abilities (I don't know the language and I don't know where and how to enter them. If something that straight-forward requires complex programming, the software is broken). This is LibreOffice 3.4 on Linux Mint. The column-based text is just copied from an xterm with the mouse. I have used OpenOffice and derivatives for years, but this one stumps me. Any ideas? Herbert I would do this: Import the data into a sheet (pasting), say column a In the adjacent column b enter the formula =value(a1) in cell b1. If you wish you can save the original data on another sheet (say Sheet2) and then the formula is =value(Sheet2.a1) -- Jay Lozier jsloz...@gmail.com -- For unsubscribe instructions e-mail to: users+h...@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] pasting numbers into Calc
At 23:36 10/04/2013 +, Herbert Fruchtl wrote: This question seems to have been discussed before, but none of the replies I understand work for me. So what have you tried, in fact? If I copy a column-based piece of text, containing numbers in some columns, into LibreOffie Calc and apply the (hopefully) correct text-to-column delimiters, ... I'm not sure what you mean here. What are delimiters? Do you mean that you have, say, quote marks around your data? Is this perhaps a .CSV file? ... all columns (including the numbers) are interpreted as text. Then you presumably don't have this part correct, in fact! On closer inspection I find that at the start of each number there is a single quote (invisible in Calc, but I can delete it with backspace, and then the number is recognized as such). That leading single quote does not really exist in the cell; instead it appears in the Input Line to show that what appears to be a number is actually being stored in the cell as a text string - exactly what you don't want. You will also notice that, by default, the numbers-as-text are left aligned, whereas true numbers are right-aligned. Doing it manually cell by cell is the only way that works. No, there is a better method: read on! Paste special as number or as text doesn't work. You cannot Paste Special *as* anything; you can select only *what* you paste. If, as here, you have text, you can use Paste Special to select whether or not you paste it somewhere else, but not to change it to numbers. Search and replace doesn't work (or I do it wrong). That's right: the quote marks are not really there in the cell, so you cannot search for them. I have used OpenOffice and derivatives for years, but this one stumps me. Any ideas? Yes. But what works will depend on exactly what is going wrong for you. o Are the relevant destination cells already formatted as Text before you paste in the material? Ensure that they are formatted as General or Number instead. o When you paste the material in, do you see the Text Import panel? Can you tinker with the options there to achieve what you need? In particular, does Merge delimiters help? What are the column types indicated in the panel at the bottom of the panel? You can click each column and then adjust the column type using the drop-down menu. You probably need Standard, not Text. o In any case, you can solve the problem with your incorrect data. Suppose your data is in column A. In row 1 of a spare column, enter =VALUE(A1) and fill this formula down the column (and possibly across columns). The VALUE() function should take your text strings and convert them to true numbers. Now copy the data from the new column(s) and paste it back over the original data, but using Edit | Paste Special... (or Ctrl+Shift+V) instead of ordinary Paste. In the Paste Special dialogue, remove the tick from Paste all if necessary, and then ensure that Numbers is ticked but Formulas is not ticked. (Note that freezing the result of a formula into a plain value in this way is something you *can* do using Paste Special... .) I trust this helps. Brian Barker -- For unsubscribe instructions e-mail to: users+h...@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
