Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index

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:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


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.