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

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)
[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
... 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.