Details : When a cell is formatted as category Text, it can never again calculate a formula automatically. Even if you reformat it as Numbers, or Date, or whatever, it refuses to calculate. Reproducible? : Yes. Confirmed. Steps to Reproduce : 1. File / New / Spreadsheet or Ctrl-N to open new spreadsheet. 2. Select cell(s). 3. Press Ctrl-1 to format cell. 4. Choose Category "Text" and click OK. 5. Enter "=SUM(7,5)" or "=sum(7,5)" in a text cell (without quotes). 6. Select cell(s) with formula(e). 7. Press Ctrl-1. 8. Choose Category "Numbers" and click OK. Actual Results : The text remains as it was, e.g., =SUM(7,5) . Expected Results : The text formula instantly converts to the result of the calculated formula (12). Additional Information : A couple times, I was able to choose Data / Calculate / Formula to Value, and got the Expected Results. This did not always work, however. At other times, Formula to Value was greyed out. Copy/Paste Only/Formula (or Paste Special) doesn't seem to produce the Expected Results, either. My only data is from this system, using Windows 10 (64 bit, running on AMD A10-7800). I've searched for duplicates, to no avail.
This isn't a bug. It works how it's intended to work. There is an enhancement request to bring text to number into LibreOffice core (currently it's an extension). If a user goes to the extent of formatting a cell as text - of course LibreOffice is going to treat it like text.
Is that you, Roy? (The IT Crowd reference) So, you're claiming: 1. A cell becoming Text is always intentional. 2. Such a change should be irrevocable. Both of these are false. Well, you can write software where #2 is true, but I doubt many users will appreciate a spreadsheet that cannot be modified more than once. There are many ways for cells to become Text without that being intentional, even with power users of LO. Users can need something to be Text, but only temporarily, with the later intention of changing it to a date or a number. More commonly, any time you insert a row or column, LO is copying the propert(ies) of adjacent cells, and that's not always ideal. Users might have intended to have clicked on the item just before, and so accidentally convert a cell, row, column or entire sheet to Text, with no obvious change that they would quickly Undo from. So, yes, any edit should be able to be changed again, with reversible results. Otherwise, you have a cell that looks normal, like any other empty cell, but yet when you insert a number or formula, it doesn't work like one. Btw, all the many "It's not a bug, it's a feature" replies are just disheartening for users like me who take our own time to research if it's already reported, and then report the bug. Eventually, someone will take this serious enough to fix it. It's a HUGE issue for anyone who uses Text. And, yes, of course I tried turning it off and back on!
Correcting status to unconfirmed.
Hi Walker, Added some reference issues..
Hi all, This problem is posing a going back to MS excel in my office. After an herculian effort only I could change the mood of the staff in my office towards Libreoffice. We need a lot of calculations for the day2day office work. At times, the cell shows only the equation. It does not get updated. If we double click the precedent cells are high lighted with colour bands. It behaves as a text cell. There is no ' character before the = sign. I can repeat this like this, 0. let in the cell A3 there is an equation "=A1+A2" (of course no double quotes) gives the correct result by adding A1 and A2 1.put a ' character in the eqn. before = sign 2.convert the cell A3 to text using format cells 3.then remove the ' character ... it shows instead of the function value, the function as a text. 4.trying to convert back from text to number format does not yield the result.. it shows the eqn. rather than the value Pl. let me know what is the workaround for this. I am using Linux and 4.3.3.2 under debian. In my office MS Winodws 8.0 with LibreOffice 5.2 also have the same problem. Rasheed
The behaviour is strange and i do not know how that happen in a document.pl. post a work around.
Hi Ahmed, (In reply to Ahamed from comment #6) > The behaviour is strange and i do not know how that happen in a document.pl. > post a work around. How do you get a real life document where formulas are formatted like that? But, if it's important, feel free to contact me to look at possibilities to extend CT2N to serve this very goal. https://extensions.libreoffice.org/extensions/ct2n-convert-text-to-number-and-dates You may want to extend it yourself too, of course. Hope that helps, Cor
I'm tempted to close as NotABug, by the way
(In reply to Cor Nouws from comment #7) > Hi Ahmed, > > (In reply to Ahamed from comment #6) > > The behaviour is strange and i do not know how that happen in a document.pl. > > post a work around. > > How do you get a real life document where formulas are formatted like that? > But, if it's important, feel free to contact me to look at possibilities to > extend CT2N to serve this very goal. > > https://extensions.libreoffice.org/extensions/ct2n-convert-text-to-number- > and-dates > You may want to extend it yourself too, of course. > > Hope that helps, > Cor Hi Cor Nouws, appreciate for your immediate reply. The issue was while importing an XL document to Libreoffice and while entering an equation to find a result based on the data in some other cells, it showed only the equation rather than the result. I tried to reformat the cell using Ctl+1 back to number format but was in vain. Let me know what is the workaround for this. by the by, How to use that CT2N command Ahamed
Created attachment 131230 [details] Formula not seen evaluated
Created attachment 131231 [details] The precedent cells are seen highlighted on double clicking The precedent cells are seen highlighted on double clicking
the CT2N extension does not give the formula back to the value instead it gives only formula itself.
(In reply to Ahamed from comment #12) > the CT2N extension does not give the formula back to the value instead it > gives only formula itself. That is what I wrote in comment #7 - sorry if that was not clear.
Closing per a Calc dev's feedback: "applying display formats never changes cell content"
For clarification: if a cell is already formatted to Text, any new cell input is forced to text content, no number, date or formula recognition is attempted. This is on purpose and works similar across major spreadsheet applications. As changing the cell format does not change the cell content, to force a new interpretation of content as formula first select the cell range in question, then change the cell format to General, then, with the selection still in place, use Find&Replace (Ctrl+H): Find: = Replace: = verify that under Options the Selection only is checked and hit Replace All.