Re: Calc data sorting problem - cell formatting appears same but mustn't be?

[Brian Barker <b....@btinternet.com.INVALID>]

At 19:20 01/07/2022 +0100, David Deeks wrote:
>I am using Calc on a MacBook Pro running OS High Sierra 10.13.6 (the 
>highest version this Mac will run). A long while back I had a 
>problem sorting data on Calc spreadsheets which boiled down to cell 
>formatting being different, solved by a friendly Brian if I remember correctly!

Could that be me?!

>But I have now had the problem occur again with the cell formatting 
>appearing to be consistent. I am attaching a copy of a spreadsheet 
>with data sorted by column I. The cells in this column all identify 
>their category/format as 'Text/@' and I have not to my knowledge 
>changed anything. Note however that from row 1813 the sort begins 
>again - I have highlighted these rows in orange for clarity.

Although the cell formatting is indeed Text, the majority of the 
values in column I are actually numbers. You can see that fairly 
simply by selecting the column and then going to Format | Cells... | 
Alignment | Text alignment | Horizontal and selecting Default from 
the drop-down menu. Your black numbers will show as right-aligned, 
with your orange text values left-aligned. I'm guessing that the 
black values were entered into cells that were formatted as Number, 
the column was then formatted as Text, and the orange values entered 
after this. Note that Text formatting will ensure that entered values 
are saved as text, but will not change the actual contents of cells 
already occupied, so your numbers stay as numbers.

A simple way forward would be to convert all the values in column I 
to numbers - thus changing your orange values from text values to 
numerical values. An easy way to do this is to select the column and 
use Data | Text to Columns... | OK. But note that you will need to 
deal with the rogue value in I1812 first, or it will scupper the 
effect. The you can sort column I as you wish.

>I have tried reformatting these 'rogue' cells. I have tried a 
>blanket reformat of both columns.

Note that changing format does not change the actual contents of cells.

I think there are also some strange effects caused by cell protection 
which may be causing confusion.

I trust this helps.

Brian Barker



---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org