Issue 125943

Summary: formula in text cell not resolved when cell format changed to all/number
Product: Calc Reporter: richardbaxter <richardbrucebaxter>
Component: editingAssignee: AOO issues mailing list <issues>
Status: CLOSED NOT_AN_OOO_ISSUE QA Contact:
Severity: Normal    
Priority: P3 CC: dcl12, oooforum
Version: 4.1.0   
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---

Description richardbaxter 2014-12-13 11:49:05 UTC
Formulae in text cells are not resolved when the cell format is changed to all/number;

1. Open Calc
2. create an arbitrary formula in A1 (eg "=A2")
3. format cell B1 as text (right click on B1 - format cells - category - text)
4. select A1 - select A1 text - copy and paste A1 text to B1
Resultant behaviour: B1 is rendered as "=A2" (the formula is not resolved in B1)

Expected implementation: 
a) LOWPRIORITY: I would have thought an escape character (eg "\=") would have to be used if an '=' (equals symbol) is needed to be positioned as the first character of a text cell
b) What is more of a problem is that changing the cell format back from text to all/number does not resolve the formula; the cell remains rendered as "=A2"
Comment 1 oooforum (fr) 2014-12-15 08:44:31 UTC
To have text cell, you can input a quote as first character.
Comment 2 mroe 2014-12-15 18:03:04 UTC
This isn't a bug; it is the right behaviour.

> 1. Open Calc
> 2. create an arbitrary formula in A1 (eg "=A2")
> 3. format cell B1 as text (right click on B1 - format cells - category - text)
> 4. select A1 - select A1 text - copy and paste A1 text to B1
> Resultant behaviour: B1 is rendered as "=A2" (the formula is not resolved in B1)

No problem: you've copied text, you inserted text => it is text.

> Expected implementation:
> a) LOWPRIORITY: I would have thought an escape character (eg "\=") would have to be used if an '=' (equals symbol) is needed to be positioned as the first character of a text cell
> b) What is more of a problem is that changing the cell format back from text to all/number does not resolve the formula; the cell remains rendered as "=A2"

That's clear. You've entered text and it stays as text. No matter which format you impose on.
Calc only knows 3 input types: text, number, formulae. Any *number formatting* changes only the view of a number or a (numerical) result of a formulae. But a formatting changes never the saved input!

3 ways to enter text:
1. first character is a non-decimal
2. first (escape) character is '
3. cell is formatted as Text
Comment 3 dcl12 2015-01-06 19:41:07 UTC
Imported CSV data. The $dollar/cents were read in as text with a ' apostrophe delimiter inserted ahead of the digits. Selecting column header say D and tried to reformat to $currency, still text. Tried individual cell, same result, no change. Tried several different ways no change. The only way could be achieved was by either removing the text delimiter or reentering the digits as overtype cell data.

I believe this defect should have the priority raised since voluminous data from checking and other downloads is extremely tedious. 

Additionally I have tried find/replace with no success to this point.

Please consider fixing this bug soon. As far as I am concerned I have verified this as an issue as described by my definition of expectation.
Comment 4 dcl12 2015-01-06 19:53:09 UTC
Additional information: To my previous entry; Also tried reformatting column as plain number from text, and no change to the digits from text to number. If there is an argument that this is expected behavior, then I suggest it be a new capability/feature for the previously stated objectives edit/chg of data format.
Comment 5 oooforum (fr) 2015-01-07 09:24:00 UTC
(In reply to dcl12 from comment #3)
> Imported CSV data. The $dollar/cents were read in as text with a '
This report is not dedicated to text import wizard.
Your problem is different.
Comment 6 richardbaxter 2016-02-04 00:44:31 UTC
(In reply to dcl12 from comment #3)
This is an example of why https://bz.apache.org/ooo/show_bug.cgi?id=57095 shouldn't be classified as NOT_AN_ISSUE. It is not obvious that plain text containing two or more dollar entries e.g. "$1000\n$2000" can't be imported into an OO calc spreadsheet and their format properly detected (Currency/Number) without selecting "Detect special numbers". I have created a new issue here; https://bz.apache.org/ooo/show_bug.cgi?id=126821.