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

At 02:56 11/11/2013 +0200, Ady Noname wrote:
> If the numbers (formatted as text) are already saved in your 
> spreadsheet, I would normally suggest a simple procedure involving 
> "paste special" and multiply (by "1"). This has worked for me in 
> several other spreadsheet tools. But Calc will (currently?) fail, 
> because Calc adds a single quotation mark at the beginning of the 
> cell. So what seems to be just "1" (without the double quotation 
> marks), in Calc actually is "'1" (without the double quotation 
> marks, but including the single initial single quotation mark).

No, the reason this fails is because you are trying to multiply text 
by a number.  If this worked, the original problem would not exist: 
the user would be able to involve his text values in mathematical 
formulae directly.  And it's wrong to think of that leading single 
quotation mark as actually being in the cell.  Preceding a numeric 
value by a quotation mark is how you indicate in typing that you want 
the value to remain as text and that you do not want the value 
interpreted as a number.  It's also the way that values shown in the 
Input Line that might appear to be numbers are indicated actually to 
be text.  But there is no quote in the cell.

> You could select the relevant cells, change their format and then 
> 'find and replace' on that same selection. But, since this is a 
> special (hidden) character, I'm not sure how to make it happen 
> ('find and replace' might not find the specific character).

Indeed: it won't find it because it's not there!

> As a simple user, I see this "hidden" addition of the initial single 
> quotation mark as a _BUG_, and as one of those basic "features" that 
> work poorly in LibreOffice Calc than in several other spreadsheet 
> tools. I don't know if this behavior can be "corrected" or improved.

Aaargh!  The quotation mark is not added to the cell, but is used to 
indicate that you have preserved the value as text, not had it 
interpreted as a number.  That's most definitely a feature, not a 
bug!  There are genuine uses for this: if you live in Newark, New 
Jersey, USA, your ZIP code may be the five-character text string 
07102.  But the postal system will not like your misrepresenting this 
as 7102 - the number seven thousand, one hundred and two.

Brian Barker


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