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:Find&Replace... [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 +0000, 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: [email protected] Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
