Brewster Gillett wrote:
Thank you for a very useful tip, which I may never have discovered
otherwise. Sure enough, the ones that aren't included in the sort are
showing as text. And, fool that I am, I should have seen it - every
single one of them has a leading single quote - an artifact whose
presence is a complete mystery to me.
The quote used to be there ever since, in Lotus 123, Excel, StarCalc and
others.
1) All input gets evaluated as number in the context of the cell's
number format locale.
2) A leading = triggers formula evaluation
3a) A leading quote supresses all evaluation.
3b) You can apply number format "@", which like any other attribute, has
no effect on existing data. It implies a leading quote for any newly
entered input.
When you paste zip-codes from a decent application (database?) the
clipboard data are marked as text and Calc will treat them as text in
this particular case.
When you enter a sequence of digits into a cell it treats the sequence
as number.
The solution the ID-number problem in *any spreadsheet* (including
Excel) is number format "@" paste-special unformatted text into
preformatted cells.
Since spreadsheets do not support fields nor records and most people
like to use database features rather than spreadsheet features, I always
suggest a most simple database table. It is by far less effort than
writing about all these spreadsheet pitfalls.
Having your data in a reasonable structure, you can still use them in
Writer and Calc. But then you can be 100% sure that all text is text,
that mandatory fields do not have any gaps, that references to growing
lists adjust properly. This makes your spreadsheets more reliable and
maintainable.
I'm sure this helps,
Andreas
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
