Bug 141057 - Calc : can't operate number text as "("&-9&")" and a number
Summary: Calc : can't operate number text as "("&-9&")" and a number
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.4.7.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-03-16 09:14 UTC by Sebastien COGEZ
Modified: 2021-03-17 14:25 UTC (History)
3 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 Sebastien COGEZ 2021-03-16 09:14:56 UTC
Description:
Hello,

When I put ="("&-9&")" in a cell, which can be a number value with a special notation, I can't use this value as a number.
It worked with anterior versions of LibreOffice, so, I think thats a bug.

Steps to Reproduce:
1. in A1 cell ="("&-9&")" 
2. in B1 cell =A1+3


Actual Results:
#VALUE! result

Expected Results:
-6


Reproducible: Always


User Profile Reset: No



Additional Info:
Version : 6.4.7.2 (x64)
Build ID : 639b8ac485750d5696d7590a72ef1b496725cfb5
Threads CPU : 8; OS : Windows 10.0 Build 19042; UI Render : par défaut; VCL: win; 
Locale : fr-FR (fr_FR); Langue IHM : fr-FR
Calc: threaded
Comment 1 [REDACTED] 2021-03-16 11:11:05 UTC
(In reply to Sebastien COGEZ from comment #0)

> When I put ="("&-9&")" in a cell, which can be a number value with a special
> notation, I can't use this value as a number.

I won't expect that to work: ="("&-9&")" is a text and from my perspective you correctly get a "Wrong data type" error. If you want numbers appear the way you did through formula ="("&-9&")", use cell formats (e.g in your case: format code: "(0.##);(-0.##)" or similar).

However: "yes" - the construct worked in 5.4.7.2, 6.2.8.2, 6.3.6.2 and does *not* work in: 

repro 

Version: 6.4.7.2
Build ID: 639b8ac485750d5696d7590a72ef1b496725cfb5
CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: kf5; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US
Calc: threaded

repro

Version: 7.0.5.2
Build ID: 64390860c6cd0aca4beafafcfd84613dd9dfb63a
CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: kf5
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

repro

Version: 7.1.1.2 / LibreOffice Community
Build ID: fe0b08f4af1bacafe4c7ecc87ce55bb426164676
CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: kf5
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded
Comment 2 Ming Hua 2021-03-16 11:42:17 UTC
(In reply to Uwe Auer from comment #1)
> (In reply to Sebastien COGEZ from comment #0)
> 
> > When I put ="("&-9&")" in a cell, which can be a number value with a special
> > notation, I can't use this value as a number.
> 
> I won't expect that to work: ="("&-9&")" is a text and from my perspective
> you correctly get a "Wrong data type" error.
I concur.  The old behavior seems wrong and I consider the change of behavior a bugfix rather than a regression.

> If you want numbers appear the
> way you did through formula ="("&-9&")", use cell formats (e.g in your case:
> format code: "(0.##);(-0.##)" or similar).
In addition, if you would like more automation, there is the TEXT() function.
Comment 3 m_a_riosv 2021-03-17 12:30:52 UTC
It assumes the '(9)' as a negative number so '(-9)' it is not a number, or it is a double negative with different formats.

(9)	="("&9&")"
-8	=+A1+1
-9	="-9"
-8	=+A3+1

I think it's fine so.
And LibreOffice has become less permissive with strings considered as a number over the time.
Comment 4 Sebastien COGEZ 2021-03-17 14:25:37 UTC
Thanks for the answers.