Calc sorting 2 columns of identical data differently- can't figure out why

[GeoJoeK]

Hi All,

I have two columns of nearly identical data I need to QAQC. Both have all cells formatted as text and contain mixed text and number values.

The issue is that when I perform a sort->Ascending on one column, it puts all of the numbers first and then lists number-text strings afterwards:

123
125
127
…
998
0H9
1H0
1H1
1H2
etc…

However, in the other column, in another worksheet (an imported CSV), it sorts the same exact data as:

0H9
123
125
127
…
1H0
1H1
1H2
…
200
202
204
…
2H1
2H2.

Etc…

Any clues? I feel like I’m missing something obvious, but have already spent a half an hour searching for an answer.

Thanks,
Joe

[Lupp]

Inspect the ranges again, in specific the cells containing 123, 125, and so on in the first sheet, and you should find they are actually numbers, not text. Sorting ranges containing a mix of numbers and texts will result in all the numbers placed in front of all the texts.
Do you know ‘View’ > ‘ValueHighlighting’ (Ctrl+F8)?

(Editing:)
Sorry! Forgot to tell that you missed to switch off the option ‘Range contains column labels’ when sorting the range in the second sheet. Sorry! See comments.

[mark_t]

Looks like the second worksheet imported from CSV was sorted as text, probably column type set to text during import. In that case the 0H9 would be first in ascending order even with “Range contains column labels” switched off. Is there some other clue that I missed that shows that option might be on? Still good to point out to @GeoJoeK to check that option is set correctly.

[Lupp]

“In that case the 0H9 would be first in ascending order even…” You are right.
“Is there some other clue…” No. Sorry! I did not look thoroughly enough.
But…
When I tested the example again with V5.2.there were strange observations. The new behaviour of appliying the ‘Text’ property to former numbers immediately after the cell’s ‘Numbers’ format was set to “@” seems not to work consistently. I got FALSE when asking =ISTEXT() in such a case.The sort algorithm got it, too…It’s a mess.

[mark_t]

I also noticed strange behaviour after changing a numbers format to text, it moved the displayed content to the left of the cell as it would for text, but it was still sorting as numbers unless I edit the content. (LO 5.2.1.2).

[mark_t]

The column that sorts all numbers first probably has the numbers formatted as numbers while the other column has the numbers formatted as text.

After you change the cell format you might need to edit the cell content to get the content recognised as the new format.

Easiest way to force format to text is add ’ at the start of each number, which also avoids editing the cell content to set the new type of the data.