At 22:55 28/06/2021 +0100, David Deeks wrote:
Thanks very much for your explanation re the Text to Columns facility Brian.
No probs!
To answer your question
For many years I have done reviews for a music
magazine and the values are quality ratings (1-5
"stars") for vinyl records and cds - three
columns denoting Music Quality, Sound Quality,
and (for vinyl) Background Noise i.e.
clicks/pops. An entry such as 2-3 means that the
tracks across the release vary from 2-3. An
entry such as 2/3 means that the first side of a
vinyl is rated an overall 2, the second side 3.
Oh, I think everyone was clear about what your codes meant.
The spreadsheet is many years old, passed on to
me and perhaps originating from Microsoft Excel.
Some of the entries in the sorted column have a
preceding ' whereas others do not. I have never
needed to "sort" the reviewed recordings by
quality rating before, but have recently needed
to do so, hence discovering the resulting
problem. All of the entries with a preceding '
sort "successfully" i.e. like this e.g. 2, then
2/3, 2-3, 3, 3/4 etc. All of the entries without
the ' do so too, it's just that they don't mix!
As previously explained, there are no single
quotes in your cells: they show up in the Input
Line just as an indication that those values are
text values in cells with numeric, not text,
formatting. If - as is clear - you need values
with hyphens and slashes that are not numbers,
then you need your values all to be text. You -
or in this case the person who created the
spreadsheet - should have made the decision in
designing the spreadsheet to format the relevant
cells as Text and to enter values as text.
For my immediate requirement I'll see to these
rogue fields by manually inserting the rows into
the required slots as you suggest. For future
use I'll have a go at modifying the spreadsheet in some way.
It's easier than that:
o Select the relevant cell range - probably an entire column.
o Go to Format | Cells... | Numbers and select Text under Category.
o Note that changing the format will *not* change
any values, so this doesn't solve existing problems!
o Select the relevant cell range.
o Go to Data | Text to Columns... .
o Under Fields, click the word "Standard" at the head of the column.
o Against "Column type", select Text from the drop-down menu.
o OK.
Now all your values should be text values
formatted as text, and they will sort as text.
This means that "2-3" and "2/3" will end up between 2 and 3, as you require.
There is one remaining problem that I mentioned
before. Because "2" as text comes before "3" as
text, anything starting "2" will sort - as text -
before anything starting "3". That means that
twenty ("20") and even two million ("2000000")
will both sort before "3". But since your values
do not exceed 5 - in other words they are all
single digits - this problem will not show for you.
While I have your attention(!), I'll push my
luck with one more query! You helped me a while
back re re-sizing of rows. My query now is, once
these have been re-sized to increase their
height so that the values are fully displayed
(whether by double clicking, using 'optimal
height' or manually) why is it that after
saving/closing the spreadsheet they re-appear as
the wrong size again, and is there a way of more permanently re-sizing?
That's not my experience or, I imagine, anyone
else's. I don't know exactly what you can be
doing to cause this. Oh, you are not saving your
document in the foreign Microsoft .xls format,
are you? I'm not sure that would be the culprit,
but it can cause problems. As Joyce Grenfell said
to George, "Don't do that". Use OpenOffice's
native Open Document Format formats - here .ods.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org
