Bug 98590 - Confusing behavior when cell with number is changed to format Text: no indication if cell value type is number or text
Summary: Confusing behavior when cell with number is changed to format Text: no indica...
Status: RESOLVED WONTFIX
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.2.0.0.alpha0+
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Number-Format Calc-Cells
  Show dependency treegraph
 
Reported: 2016-03-10 21:30 UTC by Stanislav Horacek
Modified: 2021-05-30 10:05 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Stanislav Horacek 2016-03-10 21:30:39 UTC
After resolving bug 96822 in 5.2 master, under certain conditions, one cannot quickly decide if a cell contains number or string data type. Consider this:

1. In a new spreadsheet, enter a number (e.g. 1) to A1.
2. For A1, choose Format - Cells and select the "Text" category.
3. For A2 choose the same cell format.
4. Enter a number (e.g. 1) to A2.

The number in A1 has not been converted to text (as documented in [1]) and it appears as office:value-type="float" in the ODS file.

The number in A2 is of text data type and appears as office:value-type="string" in the ODS file.

However, both numbers are left-aligned and have the same formatting code, so it is hard to identify their types.

This issue could be solved by:
- reverting the patch for bug 96822,
- adding an immediate conversion to text when format is changed (e.g. Google Sheets performs such immediate conversion).

[1] https://help.libreoffice.org/Calc/Formatting_Numbers_as_Text
Comment 1 Robinson Tryon (qubit) 2016-03-11 03:05:25 UTC
TESTING with Ubuntu 14.04 +
LO 5.2.0.0.alpha0+ (2016-02-24_23:58:47)

(In reply to Stanislav Horacek from comment #0)
> After resolving bug 96822 in 5.2 master, under certain conditions, one
> cannot quickly decide if a cell contains number or string data type.
> Consider this:
> 
> 1. In a new spreadsheet, enter a number (e.g. 1) to A1.
> 2. For A1, choose Format - Cells and select the "Text" category.
> 3. For A2 choose the same cell format.
> 4. Enter a number (e.g. 1) to A2.
> 
> The number in A1 has not been converted to text (as documented in [1]) and
> it appears as office:value-type="float" in the ODS file.
> 
> The number in A2 is of text data type and appears as
> office:value-type="string" in the ODS file.
> 
> However, both numbers are left-aligned and have the same formatting code, so
> it is hard to identify their types.

Confirmed

> This issue could be solved by:
> - reverting the patch for bug 96822,
> - adding an immediate conversion to text when format is changed (e.g. Google
> Sheets performs such immediate conversion).

Seems like a reasonable change (at least to discuss), so
Status -> NEW

Should this be categorized as an enhancement or a regular bug?
Comment 2 Regina Henschel 2016-03-11 15:09:49 UTC
You can always see the kind of entries via toggle "value highlighting" (Ctrl+F8).

Never change content _automatically_ by changing a format. You might do it after a warning hint. Another solution would be a "hint" at the cell (like the green marks in Excel) which tells the user, that there is a number in a text formatted cell.

I would prefer to revert bug 96822, because staying right aligned indicates the user, that the action "format cell as text" does not do "convert content to text".
Comment 3 Stanislav Horacek 2016-03-11 18:51:18 UTC
Yes, the highlighting is a good way to see the types (even if different colors are not as distinct as different alignments). And I would agree with the revert.

However, I am wondering: What are use cases for numbers in text format (but not converted to text)? And if they are useful, why don't we have two choices here - one for format and one for type? The current state when the format change to text preserves numbers, but causes that a new value entered in the cell will be text, is not too intuitive for me.
Comment 4 Regina Henschel 2016-03-11 19:17:29 UTC
(In reply to Stanislav Horacek from comment #3)
 
> However, I am wondering: What are use cases for numbers in text format (but
> not converted to text)?

You have already calculations in the spreadsheet and want to add explanations which might start with =, so you click on column or row header and set format text.

 And if they are useful, why don't we have two
> choices here - one for format and one for type? The current state when the
> format change to text preserves numbers, but causes that a new value entered
> in the cell will be text, is not too intuitive for me.

Yes, a UI feature to convert number content into text, which performs a real conversion, would be useful. If such exists, a wrong use of formatting would be less likely.
Comment 5 Cor Nouws 2016-10-27 09:28:03 UTC
interesting discussion for UX on the behavior of numbers, text, conversion..
Comment 6 Cor Nouws 2016-10-27 09:28:35 UTC
(In reply to Cor Nouws from comment #5)
> interesting discussion for UX on the behavior of numbers, text, conversion..

and also the idea to revert bug 96822
Comment 7 Eike Rathke 2016-10-28 12:03:15 UTC
(In reply to Cor Nouws from comment #6)
> and also the idea to revert bug 96822

Actually not. AFAIR that's also what Excel does, doesn't it? Displaying numeric content left aligned if the cell is formatted to text.

As for "adding an immediate conversion to text when format is changed" of the original request: applying a display format NEVER changes the cell content, and if Google Sheets does it that's their problem. Such behavior is completely unexpected and makes absolutely no sense if you think further and consider the layers of cell style, conditional format and hard format, in which each a number format can be applied.

Reverting the "numeric cell content is left aligned if formatted to text" argument again leads to "there's no indicator that the cell format is text if the content is numeric and input of new assumed-to-be-numeric content would be converted to text". It's going in circles and likely cell hints would be the best solution (which could be used for other scenarios like mismatching data in columns as well).
Comment 8 QA Administrators 2017-10-29 13:05:00 UTC Comment hidden (obsolete)
Comment 9 Stanislav Horacek 2017-11-16 22:04:00 UTC
Still reproducible in 5.4.1 and in:
Version: 6.0.0.0.alpha0+
Build ID: 141fe1c5e7fbf67a083b34e49e19b6ea78a0eb2b
Comment 10 b. 2020-02-29 19:47:02 UTC
this point was discussed plenty times? 

1. to take the alignment of cell content as a 'type indicator' is a weak approach / bad path as it could be manually overwritten. 

2. it is almost a question of faith, one party defends that changes by the user should not have any harmful effects on existing constructions, but accepts that errors or 'irritations' will occur later, the other side thinks that an action by the user - also a formatting - is a declaration of intent to which he expects a reaction. And this immediately, not 'sometime later' when he has long forgotten what he reformatted, when and why, or never knew about it in a table from someone else. 

3. I'm of the second opinion, if a user does damage with a format change he should see it immediately and still has 'ctrl-z' to help him out. if the damage occurs outside of his view he should be warned with a note, this should also be done with the current behaviour if later on - when evaluating the format change by changing the value - consequences in remote areas occur. 

4. Capitalized 'NEVER' and 'consider other layers' are statements, but not arguments. From a programmer's point of view there are certainly arguments for the current procedure, from a user's point of view it is 'suboptimal'. The wish to have a program / table / sheet reacting to user actions in an way intuitively  understandable to a user, even a 'simple-minded-user' is an argument. (i've lost weeks! with similar weak behaviour in excel, late evaluation of formats and 'hidden' exotic formattings not 'checkable' by the user, one can say i hate such things.)

5. Marking cells as what they are formatted to would be very helpful, regardless of the basic idea one belongs to, a clear marking if format and content do not match would be very good ... a detailed analysis of what a cell is formatted as, what it was formatted as when the content was entered, what it is currently evaluated as, and what would change with a 'touch' ... probably goes beyond the scope of a clearly arranged UI ...???

just my two cents, 

reg. 

b.
Comment 11 Heiko Tietze 2021-05-20 13:28:12 UTC
Enter 1,2 in A1,A2, select A1:A3, change the number format to Text, and add 3 in A3. All numbers are left-aligned, but count is 2. Meaning, it depends on whether the number was entered before or after. And while I dislike the behavior it is exactly the same on Excel. Plus, we have Value Highlighting. 

So my take: WF - any change will be a regression for some.
Comment 12 Mike Kaganski 2021-05-21 06:42:59 UTC
(In reply to b. from comment #10)
> 2. it is almost a question of faith, one party defends that changes by the
> user should not have any harmful effects on existing constructions, but
> accepts that errors or 'irritations' will occur later, the other side thinks
> that an action by the user - also a formatting - is a declaration of intent
> to which he expects a reaction. And this immediately, not 'sometime later'
> when he has long forgotten what he reformatted, when and why, or never knew
> about it in a table from someone else. 
> 
> 3. I'm of the second opinion, if a user does damage with a format change he
> should see it immediately and still has 'ctrl-z' to help him out.

Whenever one wants to ignore the arguments of the other side, they always have an option to call that "a question of faith". Namely, in this case the #3 illustrates the idea clearly: it's a lunacy to imagine that such a re-formatting might be seen "immediately". Only a person who never used any descent spreadsheet in production, with tens of sheets and thousands of rows, where selecting a column and formatting is differently is a normal workflow to change presentation (and doing it differently would be absolutely tedious and would took awful time) - only having no experience in real-life usage patterns, could one believe that the side effects of such a damaging proposed change could be noticed immediately. One would need to check every number in the affected cells, as well as every dependent cells and charts.

So here is not "a question of faith", but a case of one side having this idea "I don't know how the other side works; but this change would benefit me, and I believe (aha, here the "faith" appears) that the other side has not more substantial arguments than I do (and no, I don't want to take trouble to think about the other side's arguments)".
Comment 13 b. 2021-05-21 13:45:27 UTC
@Mike Kaganski, 

let's say there are two options, 

A - immediate reaction on cell format change, with a chance for the user to see that his changes have been 'accepted', 'worked', and in case of an error to see it immediately (not sure, but have a chance) and to assign it correctly to the cause, and to remove it with a simple ctrl-z and to continue working with undamaged data, 

and B: where the user's changes are not processed at first, so he thinks 'it didn't work' and 'blindly' throws other changes behind that make everything worse, only to be surprised months or years later by changes or unwanted consequences of changes he can't assign, maybe didn't even make himself, and can only correct with tedious debugging, with a good chance of stepping on other landmines he or other people have installed before or in between? 

i know that you think the second way is better, IMHO it's stupid and i regret that both the work in the sheet and the code of calc are developing more and more in this direction. 

i see one argument for option B: excel doe's it that way. but as already mentioned more than once, even if you follow an elephant, you don't have to go along with all the nonsense it spouts. To keep compatibility: two options, 'reasonable calculations' and 'Excel compatibility mode'. 

i suggest to let the users vote how hey would like to have it instead of 'experts' making stubborn decisions and forcing them on the users.
Comment 14 Mike Kaganski 2021-05-21 14:03:40 UTC
(In reply to b. from comment #13)
> let's say there are two options, 
> 
> A - immediate reaction on cell format change, with a chance for the user to
> see that his changes have been 'accepted', 'worked', and in case of an error
> to see it immediately (not sure, but have a chance) ...

Let us remember this "not sure", and keep reading:

> and B: where the user's changes are not processed at first, so he thinks 'it
> didn't work' and 'blindly' throws other changes behind that make everything
> worse, only to be surprised months or years later ...

So if one uses correct terms, there are two options: A: when user has chances to *not* see (overlook) important problems, to find out later - when it's too late, after some saves, that the data is irreversibly damaged; and B: where user is *guaranteed* to see immediately that they use *wrong* tool - formatting - for the task of converting data (for which there are *dedicated* tools). Yes, some people are too clever to read documentation, or to ask on forums, and would "'blindly' throws other changes behind that make everything worse, only to be surprised months or years later" - but that is just a very poor excuse to make changes that would make the reliable work *impossible* for others who use it *correctly*. And then - the proposed change makes it *impossible* to apply formatting changes *without* changing data.

I suggest you to simply fork LibreOffice, make the changes you want, and advertise it as the new shiny fork with this great new feature, which would surely simply make all users switch, that way voting by feet. Job done.
Comment 15 Heiko Tietze 2021-05-25 06:38:00 UTC
With this tone the discussion is going nowhere; resolving WF as of comment 11. 

@Stanislav: as the OP you may reopen the ticket but I hope you understand the reasoning.
Comment 16 Stanislav Horacek 2021-05-30 10:05:56 UTC
No objections from my side. Originally, I wanted to point out that there was a breaking change which did not make the behaviour better or worse, just different (maybe reasoned by the "excelization"). Now, after 5 years, any change in this would be counterproductive.

However, I think that good points were mentioned in this discussion and they could help from UX point of view - e.g. I like a cell hint that numeric type is formatted as text.