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

El 2013-11-11 04:00 p.m., David Gast escribió:
> Spreadsheets use the MVC (model view controller) paradigm.  That means
> that the model (how the data are actually stored) and how you view
> the data are separated.  You can take a number like 40000.5 and view it
> as a date, a date and time, a real number, etc.  You can easily compare
> dates because they are stored as numbers, not character strings like
> "Monday, Nov. 11." Further, you can easily send your spreadsheet to
> someone who only knows some language you have never heard of and s/he
> can open it and display and compare the dates in whatever language
> s/he has set.
>
> The best way to see if a cell contains a number, text, or a formula is
> to use View -> Value Highlighting (F8).  (Does Excel even have this
> feature?  If so, it must hidden in the ribbon somewhere.)  A zero as
> text has the ASCII value 48; as a number, the value is 0, so text and
> numbers are not equal.  OpenOffice used to generate errors if one
> improperly tried to add text and a number, for example.  Along the
> way, that behavior was modified to emulate Excel.  (I prefered the
> old way along with the fact that either OOo or gnumeric or both used to
> evaluate -1^2 correctly--the mathematical answer is -1, not 1.)
>
> I just checked using Excel 2010, if you change the format (the view) of the
> cell, the underlying representation (the model) does not change.
>
>     1. Type '123 in a cell, say A1
>     2. Right click and choose Format Cells, then Format as a number.
>        (That is, change General to Number.)
>
> The entry is still text.  You can confirm because =sum(A1) yields 0.
> Note: =A1+0 yields 123. (Also the text is still left justified.)
>
> That is, there is no conversion.
>
> Best regards,
>
> David Gast
>
> ________________________________________
> From: Oogie McGuire [oogiem@desertweyr.com]
> Sent: Monday, November 11, 2013 09:17
> To: Joel Madero
> Cc: Brian Barker; users@global.libreoffice.org
> Subject: Re: [libreoffice-users] CALC convert text to numbers
>
> For me dealing with an extension, installing it, making sure it doesn't conflict with something 
> else was more effort than creating a column, using Value() and then pasting special.
>
> What's a problem is that in Excel even though it also uses the leading ' to format text as numbers, 
> if you change the format of a cell the conversions happen without any problems. I want that same 
> behavior in Calc because to me it makes sense that the cell format should be the controlling factor 
> for what type of data is in a given cell.
>
>
> On Nov 10, 2013, at 7:43 PM, Joel Madero wrote:
>
> > Why is everyone straying away from the fact that there is a simple extension developed by Cor (one 
> > of our brilliant devs) which accomplishes all of this? Just curious if there's a benefit to doing 
> > these formula techniques instead of just pushing a button on a nice gui
>
> Eugenie (Oogie) McGuire
> Desert Weyr http://www.desertweyr.com/
> Paonia, CO USA
>
>
> --
> 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


With Excel 2010 I made the following test, that contradicts your assertion.

A)  Write just:  123    -->  That's a number (just to compare).
B)  Write :     '123    -->  That's text.  Sum(cell) is equal to cero.
C)  Multiply:   In another cell write a formula that
                references '123 address and multiply by 1.
                You get a number!

Check it.





--
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