Bug 132026 - Entering "-" in a cell formatted as text puts Calc into formula-entering mode
Summary: Entering "-" in a cell formatted as text puts Calc into formula-entering mode
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium enhancement
Assignee: Baole Fang
URL:
Whiteboard: target:7.6.0
Keywords:
Depends on:
Blocks: Cell-Formula
  Show dependency treegraph
 
Reported: 2020-04-10 16:01 UTC by Grzegorz Junka
Modified: 2024-05-17 13:34 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 Grzegorz Junka 2020-04-10 16:01:43 UTC
Description:
There should be a way to disable auto-entering formulas in Calc, similar to disabling auto correction. Currently entering dash "-" or plus "+" and trying to move to the next cell triggers a formula-entering mode. This behavior persists even for cell explicitly formatted as text.

Without such option it's impossible to enter dash in a few adjacent cells without workarounds, i.e. after entering "-" to press Enter, which moves cursor to the cell below, then cursor Up and Right (4 strokes instead of 2).

Raised originally here:
https://ask.libreoffice.org/en/question/237810/how-to-disable-formulas/

When I enter "-" or "+" into a cell and press cursor right to move to the next cell LibreOffice thinks that I am starting to write a formula and automatically highlights the cell I moved into. I formatted all cells as text but it doesn't change this behavior. Is there a toggle that prevents this, similar to Auto correction?

Steps to Reproduce:
1. Enter "-"
2. Press cursor right

Actual Results:
Formula entering mode.

Expected Results:
Cursor moved to the next cell.


Reproducible: Always


User Profile Reset: No



Additional Info:
The software should not enter into formula enter mode when the cell is formatted as text (menu Format -> Cells...) or should provide an option to disable this behavior, similar to disabling auto correction.
Comment 1 Buovjaga 2020-07-11 12:58:57 UTC
In AskLibO, Mike asked to create this report.
Comment 2 Heiko Tietze 2020-07-13 13:07:48 UTC
Request to optionally disable this feature was recently refused.

*** This bug has been marked as a duplicate of bug 133203 ***
Comment 3 Eike Rathke 2020-07-13 13:52:27 UTC
Actually this is not a duplicate of bug 133203 and not related to AutoCorrection but '-' and '+' being accepted to start a formula instead of '=' is a feature for those who enter data on a numeric keypad.
Comment 4 Grzegorz Junka 2020-07-14 02:58:37 UTC
Actually I also completely lost why this has been marked as a duplicate of a localization bug. Reopened. Can someone please add some reasoning?
Comment 5 Roman Kuznetsov 2020-07-14 10:23:51 UTC
Today that behavior is a feature but not a bug. It may be as an enhancement to disable "edit formula mode" when you start typing with sign minus
Comment 6 Heiko Tietze 2020-07-14 12:11:17 UTC
Reason for the feature is stated in comment 3 and I still suggest to resolve as WF referring to bug 133203 comment 8. Adding -A1 is simply done per ="-A1".
Comment 7 Grzegorz Junka 2020-07-14 23:53:28 UTC
It might be a feature for numeric cells, but when a cell is explicitly formatted as text it's an impediment into entering that text. Basically, user can't enter any text starting with or containing - or + and move to the next cell.

I never stated that this is related to auto correction. I only stated that disabling the ability of entering such text without allowing the user to enable it is a bug.

As an example, imagine that there is no option to disable auto correction and that every time user enters "WMvare" the editor changes the spelling to "Wmvare", which is incorrect. When the editor forces an incorrect behavior a feature becomes a bug.

In this case it's very similar. User can't enter "-" and move to next cell without triggering the formula entering mode.

Note, that there is no other way of moving across cells in Calc than using keyboard. Think about disability and UX issues when users try to use Calc without a mouse. A somehow helpful feature that is always forced and can't be disabled becomes a bug.

This issue is present not only when entering '-' or '+' but also when entering '--', '- ', '--- ', '===-+_-', '-- a --' ... basically even when the formula is invalid.

The simplest resolution would probably be to disable this for cells formatted as text. A proper solution would be to add a position in the menu to disable "formula mode" or how it's called, similarly to auto correct. There is also plenty of space in LibreOffice Calc - Formula tab in Options.

Sorry for this long explanation. I appreciate your work on the software. I just don't see how some custom behavior triggered by a specific combination of characters that can't be disabled is not a bug. Imagine driving a car where pressing the break, left turn indicator, and then low beam headlights, in that order, causes the airbag to blow up.
Comment 8 Heiko Tietze 2020-07-15 07:03:50 UTC
(In reply to Grzegorz Junka from comment #7)
> A proper solution would be to add a position in the menu to disable "formula mode"

It's not that I cannot follow the use case, it's annoying for me as well sometimes. But point is, Calc is a spreasheet tool and we should make it comfortable for working with numbers not text.

And I disagree with too many options, in particular exposed at the main menu or toolbars. Keep it simple!

> The simplest resolution would probably be to disable this for cells
> formatted as text. 

Could imagine this solution. Eike, what do you think?
Comment 9 Grzegorz Junka 2020-07-15 08:08:02 UTC
Thanks for looking into this.

I would argue that most people use Calc to enter tabular data, i.e. texts and numbers. The vast majority of users doesn't use formulas. Those who do most likely just use SUM. At my work I rarely encounter spreadsheets that do anything more than gather data. Some people might have heard about formulas but very few understand them. Basically someone needs to work with spreadsheets the majority of their time to use formulas to any extent.

Do you have any statistics about how often features are used, in particular formulas, and even more specifically, by entering them as starting with -/+ in a cell?

I understand the argument that it must be comfortable to work with numbers, but from my experience this is a slim minority of use cases. The majority is mixed use of text, numbers and dates with a significant usage just as a tool to work with text-based data.

I am also not after too many options but as I noted, the Options window for Formula already presents a bunch of other less useful settings that the user can turn on and off. I don't see a problem adding an option to disable auto-formula mode.

And to reiterate, if the user made the effort to format a cell specifically as text then making these cells to work as number cells with respect to entering formulas is a bit overboard.
Comment 10 Heiko Tietze 2020-07-15 08:58:06 UTC
(In reply to Grzegorz Junka from comment #9)
> Do you have any statistics about how often features are used...

Unfortunately not, planned a survey on this for some time. Let's see what the master of Calc, Eike, thinks.
Comment 11 Michael Warner 2020-07-15 23:20:37 UTC
(In reply to Grzegorz Junka from comment #7)
> In this case it's very similar. User can't enter "-" and move to next cell
> without triggering the formula entering mode.

I am able to do it in Version 6.3.4.2 by pressing tab after entering '-' or '+'.
Comment 12 Grzegorz Junka 2020-07-17 21:51:10 UTC
> I am able to do it in Version 6.3.4.2 by pressing tab after entering '-' or
> '+'.

Yes, can work as a workaround when you want to move to the next cell, but not when you want to move up, down or left.
Comment 13 ady 2023-05-08 22:46:55 UTC
I am having some difficulty understanding the inconvenience/problem. I'll write here a certain procedure, and I'd like to know what is exactly that I am missing.

1. Select an area (or the whole spreadsheet) and format cells as text.
2. In the first cell, press the minus sign.
3. Press [ENTER]. The cell now contains a minus and the focus moved to the cell below it.

* If instead of moving down, I want to move up, then step 3 would be to press [SHIFT]+[ENTER].

* If instead of moving down, I want to move right, then step 3 would be to press [TAB].

* If instead of moving down, I want to move left, then step 3 would be to press [SHIFT]+[TAB].

* Instead of the minus sign, the same goes for the plus sign.

The only possible inconvenience here would be if the cells are not adjacent and I want to go directly from one to the other without using [ENTER] or [TAB]. But even in that case, I could just copy+paste.

So, for adjacent cells, the procedure is not complicated. Only for non-adjacent cells there is some minimal extra work, or copy-paste can be used.

I am not seeing the use-case that is not overcome by [ENTER], [TAB] and [SHIFT] keys instead of using the arrow keys. Perhaps these keys are not as comfortable or as close to a numeric pad as the arrow keys. Is that it?
Comment 14 Heiko Tietze 2023-05-09 06:50:02 UTC
(In reply to ady from comment #13)
> I am having some difficulty understanding the inconvenience/problem.

Grzegorz wants to insert a minus or plus character but starts formula editing. Comment 3 is pretty clear in WF, and quoting is always possible. We should resolve this request as WF/NAB.
Comment 15 ady 2023-05-09 08:49:38 UTC
(In reply to Heiko Tietze from comment #14)
> (In reply to ady from comment #13)
> > I am having some difficulty understanding the inconvenience/problem.
> 
> Grzegorz wants to insert a minus or plus character but starts formula
> editing.

But if that is all, I've just posted a workaround (use those keys instead of arrow keys) which has been well-known for decades. So, my thinking was that there must be some other initial situation or use-case that triggered this report, and I wanted to understand/know what _that_ was.
Comment 16 Stéphane Guillou (stragu) 2023-05-22 17:43:18 UTC
This is very much related to bug 129369, in which Eike already mentioned it could be resolved with an extra option to turn off formula mode when starting with +/- (see bug 129369 comment 7). Possibly a duplicate if we agree it should be turned into an enhancement request.

A cell formatted as Text will still enter formula mode when starting with "=", so that's not specific to -/+. And this is the case since OOo 3.3 times.
Comment 17 Baole Fang 2023-05-28 15:35:48 UTC
I've submitted a patch [1] for this ticket according to [2]. It should disable "=","+","-" formula in text cell. Not sure if it is expected.

[1] https://gerrit.libreoffice.org/c/core/+/152356
[2] https://bugs.documentfoundation.org/show_bug.cgi?id=131638#c3
Comment 18 Commit Notification 2023-06-02 18:03:14 UTC
Baole Fang committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/0450c7177f61ba5be15f7b4175b9fb5e89f850ae

tdf#132026: Fix selection in text cell

It will be available in 7.6.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 19 Commit Notification 2023-06-05 13:32:45 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/b0d49beb1b9b875e82c98439587fcf37b56050b4

tdf#132026: sc: move UItest to CppUnittest

It will be available in 7.6.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.