Changing Data Types, Sorting Problem, UI BUG

English
,
#1

This seems like a huge UI bug. I entered some data and then tried to sort it. LibreCalc sorted it wrong. Using the internet I was able to find many other people who had this error before me. The problem is invisible until the user presses “Ctrl-F8”, or goes to the View Menu and selects “Value Highlighting”. However that doesn’t fix the problem, only reveals the issue. The obvious solution of highlighting the column or columns and Formatting the Cells to all be the same data type doesn’t have any effect. The solution I found in the forums works, but is very esoteric; I imagine for a regular user this would seem like wand waving and magic words:

LibreCalc_Bug
LibreCalc_Bug876×715 208 KB

If I understand this, the Find and Replace operation takes the text of the cell and re-renters it allowing the data type to be reset to the default type for the column.

This is a major UI bug. Is there something I missed?

If you are a user and you agree this is terrible default UI behavior, and that such a strange esoteric operation to correct the error being the only (?) option is also terrible. Please help me and speak up! Leave a comment, or help me submit a bug report.

Thank you!

#2

No it isn’t. Sometimes you need to sort numbers and text in the same column and generally separated. If you do want to sort them together then in the Options tab of the Sort dialogue, tick the box Enable natural sort.

EnableNaturalSort
EnableNaturalSort660×581 15.7 KB

For other methods of converting text to numeric data see, Frequently asked questions - Calc - The Document Foundation Wiki . You might find the Data > Text to columns interesting.

2 Likes
#3

Note that you may find that even larger number of people find math very esoteric when learning. That doesn’t mean they are right.

You must learn the tool to use it. Only when some property of the tool is truly incorrect, should it be changed; when it only seems so to people who don’t yet know it, it is just the learning curve.

1 Like
#4

Maybe…

it is invisible, because you/somebody told Calc to hide it, by centering the column. Default would show this immediately. (There is also a second hint, if you see the tiny ’ before a value, but I admit it is not standing out.)

because formatting never changes the type/content of a cell. It just tells Calc to show the existing value in another way. This can show 2,-- € or $ 2.00 instead of 2 but the value is still 2 as a number.

It will show a date readable for you out if 44567 and add high noon for 44567.5 but this will stay a number, available for calculations like “how many days between”, because this is why some of us use Calc


Auto-conversion could loose data for zip-codes 01234 or phone-numbers +49 30 12345678

The main question: Where did the different type of data come from. Imho main source of error is copy/paste and not setting formatting for the columns before entering data, even as paste will override.

Forget it. Only small changes are possible here, because spreadsheets behave like this since very long time, so big changes may break existing code, and some people don’t like this as you may see in this angry guy:

However you could file a request for enhancement at bugzilla for a less esoteric “change column type” entry jn the menu. But don’t complain about to long menus afterwards

2 Likes
#5

Re: Wanderer

Thank you for the answer. I love long menus! I still consider it a user interface bug, The number two is unequivocally a number. Without some sort of contextual clue “2” and “2” should by default sort the same way.

#6

This isn’t a math problem, it’s a user interface problem. The property’s of the tools are fine. The user interface isn’t just there for those who have already learned it, it is there for those who are learning to use it. Do you use a Das Keyboard?

EDIT: Sorry if that was mean, I though it was a good counter point, but I should have mentioned it with out referring to you. I think the blank Das Keyboards are really quite cool. :slight_smile: But I wouldn’t ship them by default.

#7

Really? Are you sure? And “2” and 2 and “two” should come immediately after 199 and before 212? Okay, so be it. Just please clarify in what order the sorting should place these values - should “two” be before 2 or after it?

#8

I thank you for the tip about the Natural Sort! But that does not effect this issue, I just tried it with natural sort enabled and there was no difference.

I agree that the sort behavior isn’t incorrect. I say it’s a user interface bug because the interface is giving results that make no obvious sense, not that the results are incorrect. The user interface is there to help the user achieve their goals. In this case it is getting in the way. It looks like (almost) all the tools exist, but there is a long way to go on making them useful.

I think the interface could be improved, maybe in one or more of these ways:
There needs to be a way to see the data types, it should possibly be on by default.
There needs to be a non ridiculous way to select a cell and change it’s data type from number to text, or any other type.
It might be nice if the Sort window had a YWSIWYG or a data type agnostic sort option. - Not my first choice, I think such a thing might be neat to have, but wouldn’t be a solution to this issue.

#9

Don’t be mean, please disagree with me, but there is no need to patronize. I’m talking about a user interface issue. The data type is hidden, yet the sort keys off of it without indicating this behavior to the user.

#10

See my screenshot for default appearance of numbers vs text

#11

If the only issue that you raise here is the UI for data type change: the Text to Columns tool is the tool for this. Its name could be possibly improved; but it doesn’t need any “esoteric” incantations, and it is mentioned in the FAQ.

1 Like
#12

I’m sorry if I offended you by using the word esoteric. I definitely don’t consider regular expressions to be magic. I felt the solution was esoteric because of the way regular expressions were being used to modify the data type by reading the cell and writing it again. I would consider any solution that displayed the data types and allowed them to be modified to be non-esoteric.

I just tried the Text to Columns tool, it did get the numbers sorted, but I have no idea what it did. I still have no clue what data type the contents of the cells are. Are they text, are they numbers? I chose English(US), they got sorted, and Ctrl-F8 now turns them blue. … It’s still a user interface mess.

#13

I’m glad it works for you. When I made my spreadsheet I did nothing that should have hidden anything, I didn’t even change the justificaton of the contents of the cells. I personally think the data type could be obvious and mutable, and the sort options and/or results could indicate what was about to be done and/or what occured in a better way.

#14

What is your expectation? The Value Highlighting tool is intended to tell you this info. The default cell alignment is also there to help you with this. There is Help on all of these … but you seem to expect something like a UI that would be “intuitive” … so where is the mockup of the desired UI?

#15

I think a user should be able to find out and modify the data type of a cell, maybe via a right-click menu, maybe as part of the formula bar, maybe as part of the status bar, maybe somewhere else. Although I’m not sure what to search for “data type” comes up with no results. I have no idea how to make a UI mockup, sorry. Do you have any examples or resources I could look at?

My hope is that when sorting a column with multiple data types some indication could be shown to the user that this has happened. I’m assuming there are LibreOffice UI design standards or guidelines. Maybe something unobtrusive and something that can be disabled easily without having to enter a menu. I don’t expect that, but it sure would help new users.

My expectation is that nothing will change. Sorry if that’s a bit cynical, I’m just one more user confused and frustrated by a blind spot in the UI. Thank you for taking the time to interact with me.

#16

No; I myself would simply make a screen copy, and draw some rectangles to show my idea in Gimp / Paint / whatever.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.