At 15:36 28/02/2010 -0800, Brewster Gillett wrote:
Brian Barker wrote:
Either that or the care taken by the user? Let's be fair: you need
to test and reproduce such a problem before you can rightly assume
that it is a bug.
Right you are, Brian - of course it's more a matter of ignorance
than of lack of care....
I hope that didn't come across as dismissive or overly critical ...
As far as I've ever been aware, most any spreadsheet defaults to
numbers if that's the first thing you type into it - only formatting
as text if the first-typed character is non-numeric. Have I been misinformed?
I don't claim to understand the details of this. Things get more
complicated when you paste material in.
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.
That - which should display only in the Input line, not in the cell -
indicates a text entry in a number-formatted cell.
Let me elaborate on one of the still-puzzling aspects of this - I
only have two ways I can have a ZIP code end up in this
spreadsheeet. Either the name comes in as part of a .CSV file
downloaded from our National Office website, or it comes directly to
me from an inquiry by phone or email. In the former case, the .CSV
file gets saved as an OOCalc file. In the latter, I would have
manually typed in the ZIP.
But here's the part that is bizarre; the split between those with
leading single quotes and those without does not even close to line
up with the split of sources just described! IOW Some of the ones I
manually typed show up as numbers, and some as text. And likewise,
some of the .CVS imports show one way, and some the other.
I submit to you that that makes no sense at all... and I still have
no idea how those with the leading single quote got that way.
As I say, I don't claim to understand all the possibilities. But
what you have there are text entries in cells not formatted as text.
Now here's a whole new question that this has triggered - I have
read completely through the Help screens regarding Find and Replace,
and nothing speaks to why Find & Replace is unable to process these
entries. So thus far it looks like I am obliged to strip off the
leading single quotes manually, cell by cell. Apparently Find &
Replace cannot "see" a text entry like '97103 .
That's because the single quote mark is not really there: it is a way
of indicating - by you when entering values and by Calc when
displaying what is there - that the actual content is text instead of a number.
But you don't need to change these values manually. In a new column, enter
=VALUE(xx)
and fill or copy it down the column. This will take both your text
and number values and produce numbers. Now copy the results and
paste them back over the original results, but using Paste Special
instead of ordinary Paste; in the Paste Special dialogue, ensure that
Numbers is ticked and Formulas is *not* ticked.
Mind you, ZIP data should probably all be text, in fact. Try
=TEXT(VALUE(xx);"00000")
instead.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
