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