Date: · Thread:
2013 Archives by date, by thread · List index

# Re: [libreoffice-users] Re: CALC convert text to numbers

```
```
```On Mon, 11 Nov 2013 20:54:54 -0600
Denis Navas Vega <denis.navas@gmail.com> wrote:

```
```Paul,

Make this test.

1. Open a new worksheet.  Format some cells, say from A5:A10 as text.
Write some numbers on those cells.
```
```Ok

```
```2. Copy those cells to other column, say to C5:C10 and format as
number (format @).
```
```The '@' format is text, so I'm not sure what you want me to do, format
as text, or format as number?

```
```You will see that the cells now shows an ' before the numbers.
```
```Yes, if I format as number, this is correct.

```
```3. Write a formula in other cell, multiplying with 1, for instance,
E5=C5*1
```
```Ok

```
```Now check the cell and you will discover that you have a number.
```
```True enough, when I copy these cells, and paste special, pasting only
text and numbers, not formulas or all, then I get numbers in the cells.

```
```Therefore, those numbers with ', that in reallity are text, can be
multiplied by 1, to transform it to a number.
```
```As Brain explained to me, an implicit VALUE() must be done on the text
when multiplying by 1.

```
```As a side note, I was not able to use the method of copy -- paste
multiplying by one.
```
```How do you mean? When is the multiply done? If I copy the numbers from
the cells with the formulas it works fine.

So now I'm confused, if this *does* work, why was Ady complaining
about it not working?

--
```
```
Well, the procedure I mentioned involves copying one cell only, which
means it is a faster method, as oppose to having to copy perhaps a
lot of cells (e.g. a "whole" column; or having to add many new
"VALUE" formulas; or having to add a new column and multiply by 1
each cell and then paste special...).

I repeat the procedure that I posted before (which works correctly in
other spreadsheet tools):

1_ In an auxiliary non-formatted cell, insert the number 1.
2_ Copy that auxiliary cell.
3_ Select the cells with numbers that are currently formatted as
'text' that you want to convert.
4_ Paste special (all), multiply.
5_ Delete the auxiliary cell.

If the desired format is not just a 'general' number, you could
optionally format the auxiliary cell before copying it.

Although the "Tools -> Text to Columns..." method in Calc is nice,
the procedure I am describing is more flexible. Unfortunately, it
currently doesn't work in LibreOffice Calc 4.1.3.2.

Regards,
Ady.

--
To unsubscribe e-mail to: users+unsubscribe@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
```

### Context

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.