Download it now!
Bug 98362 - Behavior interpreting input: 1-, 1+ are treated as number, not as text
Summary: Behavior interpreting input: 1-, 1+ are treated as number, not as text
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: needsUXEval
Depends on:
Blocks: Number-Format Calc-UX
  Show dependency treegraph
 
Reported: 2016-03-02 20:33 UTC by teo8976
Modified: 2020-09-10 12:41 UTC (History)
5 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 teo8976 2016-03-02 20:33:52 UTC
Steps to reproduce:
In a cell, type "1-" or even " 1-" (without the "")

Expected result:
the input should be interpreted as text, and whatever the f**k I have typed should be stored in the cell

Observed result:
The cell now contains the number -1, which is neither what I typed, nor the  result of interpreting the input in any sensible way.

"1-" is neither a formula that yields -1 as a result, nor a representation of the number -1.
Comment 1 teo8976 2016-03-02 20:35:48 UTC
Also, "1+" becomes "1"
Comment 2 Joel Madero 2016-03-02 20:36:11 UTC
(In reply to teo8976 from comment #0)
> Steps to reproduce:
> In a cell, type "1-" or even " 1-" (without the "")
> 
> Expected result:
> the input should be interpreted as text, and whatever the f**k I have typed
> should be stored in the cell
> 

First warning on the language. See https://wiki.documentfoundation.org/QA/Bugzilla/Policies_and_Procedures

Entirely unhelpful.
Comment 3 Joel Madero 2016-03-02 20:44:37 UTC
To me this is a toss up whether we want that behavior or not. 

Setting to ux-advice so they can decide if it's a bug.

FWI - you can get the desired behavior by just formatting the cell as text. The algorithm assumes you're using calc for numbers (which is generally a decent assumption) so if you only have 1- it's treating it like a number, different users will have different expectations as to whether that assumption is right or wrong. So, default behavior should be decided by UX crew.
Comment 4 teo8976 2016-03-02 21:19:28 UTC
Yeah but what do you mean by "treat like a number"??

If you type "1 ljsbf" you don't expect it to be transformed into "1", do you?
(it doesn't happen)

If you type "lsdls" you don't expect it to become "0", do you?
(it doesn't happen)

So why should "1+" become 1, and even more absurd, why should "1-" become -1?


In other words, Calc usually "assumes you are entering numbers" **unless** you enter something that cannot be interpreted as a number.

In what notation is "1+" a representation of 1 as a number, and "1-" a representation of -1 as a number?

Your argument is valid for an input like "1e2" (which one may enter with the intention of entering text but is also 100 in exponential notation), but "1-" is not a number in any way, I don't see how anybody could expect it to be interpreted as -1.
Comment 5 Yousuf Philips (jay) (retired) 2016-03-02 21:24:16 UTC
So i tested other spreadsheet apps and Excel, WPS, Quattro Pro and Google Docs kept it as text, while Calligra and Gnumeric changed it to a number.

I'd assume the best person to explain this behaviour and whether its a bug is a Calc dev. @Eike: Any thoughts?
Comment 6 Cor Nouws 2016-03-02 23:19:11 UTC
I've made the summary a bit more realistic ;)
Comment 7 Carlos 2017-04-03 19:54:47 UTC
I can confirm that the behavior is present in
Version: 5.3.1.2 (x64)
Build ID: e80a0e0fd1875e1696614d24c32df0f95f03deb2
CPU Threads: 4; OS Version: Windows 6.19; UI Render: default; Layout Engine: new; 
Locale: en-US (en_US); Calc: group

I changed the status to NEW. The testing done by Yousuf Philips (jay) is enough for me. Compatibility with MS Excel is a priority.
Comment 8 QA Administrators 2018-04-04 02:31:28 UTC Comment hidden (obsolete)
Comment 9 QA Administrators 2020-04-04 03:31:01 UTC Comment hidden (obsolete)
Comment 10 Roman Kuznetsov 2020-08-29 19:24:40 UTC
still repro in

Version: 7.1.0.0.alpha0+ (x64)
Build ID: 15988fe00c4f49ae32ab2d5cf3c16495ab6e1d59
CPU threads: 4; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Vulkan; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: CL
Comment 11 Eike Rathke 2020-08-31 10:33:03 UTC
There's a reason for this behaviour. In some locales negative currency amounts are displayed with a trailing minus sign and of course it's expected that such data is accepted as numeric input. We maybe could make that locale dependent.
Comment 12 Thomas Lendo 2020-08-31 10:55:06 UTC
(In reply to Eike Rathke from comment #11)
> There's a reason for this behaviour. In some locales negative currency
> amounts are displayed with a trailing minus sign and of course it's expected
> that such data is accepted as numeric input. We maybe could make that locale
> dependent.
I support that to make it locale dependent.
Adding needsUXeval to have a broader input for this.
Comment 13 Heiko Tietze 2020-09-01 09:35:41 UTC
You can force 1- with cell format as text. But this would still be interpreted as a number. Since I don't see any good use case for 1- being pure text I vote for WF.
Comment 14 Thomas Lendo 2020-09-08 19:14:12 UTC
I assume the program should make the work for users as efficient as possible. If somebody types a non-number-only input into a cell, then Calc should not change the input but the cell format to text e.g. for "1-" and "1+" in English or German. If I want a number I've to use the format that is used in that region! If I want -1 I must write -1.
Comment 15 Cor Nouws 2020-09-08 20:16:54 UTC
(In reply to Heiko Tietze from comment #13)
> You can force 1- with cell format as text. But this would still be
> interpreted as a number. Since I don't see any good use case for 1- being
> pure text I vote for WF.

+1
Comment 16 Heiko Tietze 2020-09-10 12:41:19 UTC
Eike explained the reason in comment 11 and there are easy "workarounds". Let's resolve this as NAB.