Thanks again Brian. I’ll change the columns to text as you suggest.
The spreadsheet is saved as .ods. Perhaps as I think it originated as a .xls
this explains the problem of rows reverting to wrong size. At some point, if
‘life’ doesn’t get in the way, I may have a go at re-building a .ods version
from scratch!
All best
David
> On 28 Jun 2021, at 23:36, Brian Barker <b.m.bar...@btinternet.com.INVALID>
> wrote:
>
> 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
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@openoffice.apache.org
For additional commands, e-mail: users-h...@openoffice.apache.org
