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
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.