Re: [libreoffice-users] pasting numbers into Calc

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: users+help@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