Description: If in a column there are cells with a number as text they are not ordered correctly. Opening the same file with excel, a cell as this has a warning on it. The warning is "number stored as text" Reformatting cell has no success, column not ordered correctly. Steps to Reproduce: 1.Problem appear on imported spreadsheets 2. 3. Actual Results: Column not correctly ordered Expected Results: The same as before Reproducible: Always User Profile Reset: No Additional Info: Ordering with Excel asks if it must use those cells as number or as text.
Created attachment 185490 [details] File with cells "number as text"
IDK what is in these cells that makes them be sorted in this way: 931425 931426 395006 395007 601066 Maybe there is sth in "601066" or in the cell? If I select them, ctrl+c, ctrl+n, paste special as unformatted text, sort, then they get sorted as: 395006 395007 601066 931425 931426 as expected. They also get sorted as expected if I copy them as numbers. Whether there is a bug somewhere, or whether the original cells include some "hidden" character or some additional attribute, IDK.
Starting with: 931425 931426 395006 395007 601066 Applying the N() function: 931425 931426 0 0 0 Applying ISTEXT(): FALSE FALSE TRUE TRUE TRUE So clearly there is something different in some of these values/cells.
The first two values 931425 and 931426 are numeric, the other three are text. Numeric sorts before Text. You just don't see it because you formatted everything as left-justified. Activate View -> Value Highlighting (Ctrl+F8) and you'll see. Not a bug.
This doesn't seem to be a problem in sorting, but rather a problem with alignment and the type of content. All cells with data in column A are aligned as "default" and the number format is "text" (@), but the first 2 cells seem to be not treated as text by ISTEXT(), which explains the resulting sorting. This is a problem!!! When clearing the format (CTRL+A, CTRL+M), it is clear that not all the values are formatted in the same way, but the format cell dialog didn't show it at first. Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: c3bd52f81bf733a0b9b0560794a54b2ac1e0f444 CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win Locale: en-US (es_AR); UI: en-US Calc: CL threaded
Emphasize: the first 2 cells are "number", but the aligment and the number format are inconsistent with that.
(In reply to Eike Rathke from comment #4) > You just don't see it because you formatted > everything as left-justified. That's not what I see, FWIW.
(In reply to ady from comment #7) > ... > That's not what I see, FWIW. How do you know how they were aligned? You are assuming Calc did it, why? There is no bug here. To put data in place, the reporter can select the column and use Menu/Data/Text to column - Ok.
(In reply to m.a.riosv from comment #8) > (In reply to ady from comment #7) > > ... > > That's not what I see, FWIW. > How do you know how they were aligned? You are assuming Calc did it, why? I am not assuming anything. Default <> left aligned. Calc is not showing left-aligned, but "default". A numeric value that is recognized as "default" would be displayed aligned to the right. This is not shown in this case in Calc. I'm not going to reopen this again, but there is some inconsistency from the POV of a common user. The inconsistency is not in the sorting, but in the shown alignment for these numbers vs. the "default" alignment (shown in ctrl+1 dialog). The fact that ISTEXT and Value Highlighting are showing the difference is not the first step that a common user would follow. The apparent left alignment (which is incorrect for "default") _is_ a problem.
(In reply to ady from comment #7) > (In reply to Eike Rathke from comment #4) > > You just don't see it because you formatted > > everything as left-justified. > > That's not what I see, FWIW. I was mislead, it's not formatted as left-justified, but the Text number format is applied to all cells of column A, which also left-justifies output of numeric content. Presumably the Text number format was applied after the first two numeric values were entered.
The real problem is that if you ask for properties all cells result as text, but when you sort them someone are see as numbers. I, as user, think this is due to an hidden property that I can't see so I can't understand why sort don't work well.
(In reply to ady from comment #9) > The > apparent left alignment (which is incorrect for "default") _is_ a problem. No, it's on purpose. It also indicates the Text format and that further number input would not result in numeric content (note also that there is no leading ' apostrophe indicator in the Input Bar for Text formatted cells), and IIRC Excel does the same. Fwiw, that was bug 96822.
(In reply to Eike Rathke from comment #12) > (In reply to ady from comment #9) > > The > > apparent left alignment (which is incorrect for "default") _is_ a problem. > No, it's on purpose. It also indicates the Text format and that further > number input would not result in numeric content (note also that there is no > leading ' apostrophe indicator in the Input Bar for Text formatted cells), > and IIRC Excel does the same. Fwiw, that was bug 96822. I understand the logic. But, from the POV of a common user, there is still an issue here. For the first 2 cells, the format is Text, but the content is not recognized/treated as text. The cells bellow those 2 first cells are both, formatted as Text and recognized/treated as Text. So, for the common user, _something_ has to be displayed or behave differently in a more evident way. If a user has to apply some action or some formula in order to realize that these cells are not going to be treated in the same way, then I would say that's too-late. Using left alignment as default for values that are really formatted and treated as text is OK. In fact, that is one of the first indicators that a "number" is not really a number, but Text. But then an equivalent behavior goes for values that are treated as Number. Yes, the first 2 cells _seem_ now formatted as text, but the values are not treated as Text, so they should still be right aligned when they have the alignment as "default". Alternatively, the alignment for these cells should not be "default", but actually set as "left".