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

> Hi Ady,
>
> On Thu, 14 Nov 2013 15:45:33 +0200
> Ady <ady-sf@hotmail.com> wrote:
>
> > On one hand, this method is effective, efficient and well-known in 
> > several other spreadsheet programs, and for users that already know 
> > it, it seems at least strange that LO Calc doesn't support it.
> I'm still not sure it is "correct" behaviour, but you do have a strong
> argument.
 
 
There is no "correct" behavior for this. The possibility to interpret 
/ parse the content of a cell as a number (e.g. for usage in other 
cells) even when the original cell is formatted and displayed as 
'text' is a feature, which is already present in other spreadsheet 
programs. A theoretical requirement to have to *always* use "VALUE" 
for any and all usage of those 'text' cells would trigger a natural 
enhancement request to make it easier. You can use "VALUE" if you 
think it is needed for some situation, but experience says that there 
are many simple cases where imposing its usage would negate common 
sense. There is a reason why such common feature has been present in 
spreadsheet programs for decades now.

Perhaps thinking about it this way might help:

1_ In an auxiliary 'number' cell, insert the number 1; [ENTER].
2_ Select that auxiliary cell.
3_ Copy that auxiliary cell.
4_ Select the cells with numbers that are currently formatted as 
'text' that you want to convert.
5_ Paste special (all), multiply; OK.
 This "paste special" step is performing the following actions:
 5.1_ It pastes first the "cell format" from the auxiliary cell, 
converting the selected cells from 'text' format to a new 'number' 
format; and,
 5.2_ It multiplies the content of the selected cells by the content 
of the auxiliary cell (by "1" in this case); and,
 5.3_ It adds all other characteristics of the auxiliary cell to the 
selected cells (e.g. comments).
6_ Delete the auxiliary cell. 

To be clear, the 'text' "format" of the cell shouldn't block the 
multiplication (in the above procedure or when using its content in 
other cells), since the *content* of the cell should be independent 
of the way it is being displayed in the cell. You should be able to 
use that same content in whichever way you want, whether you display 
the cell with leading zeroes, decimal places, as text, or in yellow.

I don't know if LO Calc actually performs these actions in this way 
and order. I am just trying to explain why this feature makes sense 
(as it does in other spreadsheet tools).
 
> > (BTW, I still think something "fishy" is happening with the single 
> > quotation mark in LO Calc.)
> The single quote is a red herring. It is correct behaviour, and the
> same as other spreadsheet software. It is the implicit conversion
> of the text to a value that differs.
 
 
We are going in circles. The example that "Denis Navas Vega" already 
gave (with steps that you already followed) shows that the initial 
quotation mark is not necessary when you format the cell as 'text' 
before inserting its content. We all agree that the initial single 
quotation mark should not be part of the cell content; it's just an 
optional "formatting aid" to be used when fits the need.

Yet, when in LO Calc you manually convert a cell (that you first 
formatted as 'text' and then inserted a pure number, without "'") 
from 'text' to 'number' (with ctrl+1, number, general), the 
*previously nonexistent* initial quotation mark is *kept* (or rather 
*added*); it shouldn't!!! (a.k.a. BUG)

That's the difference between LO Calc and other spreadsheet tools 
where the "multiply by 1" conversion works as expected.
 
> > On the other hand, there are (less efficient) alternatives to 
> > eventually get to the same final result in LO Calc.
> And perhaps more "correct" ones. Correct in the sense that I'm not sure
> implicit conversions should be done on values. But it is a convenient
> shortcut...
 
 
See my prior rant about non-existent "correct" behavior vs common 
sense feature for this case.

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