Download it now!
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: NEW
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: needsUXEval
Depends on:
Blocks: Number-Format Calc-Cells
  Show dependency treegraph
 
Reported: 2016-03-10 21:30 UTC by Stanislav Horacek
Modified: 2020-03-02 15:15 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


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.