I fail to see how that could be possible. Also, IMHO such behaviour was never submitted as bug. One should assume if that was a common behaviour it should had shown up as a bug report. If it happened only for specific function names then it may have been a translation error where the “word” of a function included a leading space. But then again the parser should had eaten that before… What UI language with translated function names were you using, if any?
I have a similar issue with a ODS where the formulas don’t work but what I have noticed is despite explicitly Ctrl+1 set Category: All, set Format: General, OK., it doesn’t change the format of the column from Text and formulas still don’t work.
If you select a cell range that already has multiple different number formats assigned then invoking the number formatter displays General and selecting the General format will not change anything. You either need to temporarily set an explicit other format and after that invoke the number formatter dialog again on the same selection and set the General format, or remove all attribution on the selected cell range with Ctrl+M or context menu Clear Direct Formatting.
Note also that you have to re-enter a formula (editing the cell and appending and removing a blank is sufficient, or for a cell range with Find&Replace replacing = with = achieves the same) after the Text format was removed because the cell content is still text, not formula.
This is still true as of 22.214.171.124. Personally, I consider this to be a minor bug because I think any reasonable person that selects a cell or cells containing data, right-clicks “Format Cells,” selects Number > General, should reasonably expect that data to be re-evaluated whether it is a number/formula or not. Sorry I don’t have time to submit this bug report right now, but I’m commenting here to find this solution again in the future as I no doubt expect myself to waste 15 minutes troubleshooting this again in the near future.
No. Formatting a cell must never change the cell data.
You may apply formatting to large portions of sheet. E.g., select a whole column, and apply another format, to see if that fits your liking. And when you select that whole column, you may have selected some intermediate heading cells (imagine a heading cell with some ID consisting of 20 digits, which would happily convert to a number if it were allowed, but would only keep 15 digits of precision then). You do not want to discover that when you applied bulk-formatting, you also inadvertently destroyed some cell data (which you discover a week later).
Formatting and data are orthogonal. Cell data is only defined at input time, not at formatting time.
Please don’t waste time submitting that; there were a plenty of such requests, which indeed seem reasonable at a first glance, as long as one only considers the simplest cases, and doesn’t consider the fact that spreadsheets are designed to handle thousands of rows of data. Such bug reports are all closed NOTABUG.
More examples of texts that could be ruined by automatic conversion to number:
A phone number entered like +7-123-456-78-90 would become a formula =+7-123-456-78-90 with shown result of -740.
A fraction-like text entered like 1 7/14 would become number 1.5, shown as 1 1/2 (you can’t reconstruct the original denominator). For 1 15/10, the result would be 2 1/2.
A string like 1234 567 (ru-RU) or 1234,567 (en-US) - maybe some coordinates? - would become 1234567 (the placement of character separating two numbers, that happen to co-incide with group (thousand) separator, is lost).
Similarly, 123456789.123456789 (en-US) would become number 123456789.123457 (tail lost).
A string like 555e456789 would become 2E+308 (e being interpreted as exponent separator, resulting in maximum representable value, having nothing in common with original text).
A string like 1/6 would become 01/06/2022 (en-US), and shown e.g. like 44,567.00 when incrementing decimals.
A string with leading/trailing spaces around a number would indeed loose the spaces. If you have strings where original spelling is meaningful (say, you count frequency of different ways to represent something on writing), you loose the information.
0123 would loose its leading zero, breaking cases where it’s significant (e.g., when the leading zero designates octal numbers in C-like programming languages).
dec 1 (a decrement operation?) would become 12/01/22 (en-US).
true string would be recognized as a boolean value, convert to 1, and would show as, say, 1.00 when user increments decimal digits.
Data conversion is a destructive process. Data formatting should not destroy the data.
Well the way that this has been implemented is so counter-intuitive that I am back here for the second time this week looking for the solution. Can you explain to me again why LO Calc does not implicitly assume a cell with a properly formatted formula should be evaluated to obtain a result?
I’m disgusted at the thought I’m about to praise MS for anything, but Excel forces the user to prepend properly formatted formula text with an apostrophe (’) to signify “do not evaluate.” I am not trying to be that user that complains “Why can’t LO be more like MS Office,” but I’m struggling to keep an open mind right now.
TL;DR: Would you please explain the benefit from making the cell format control whether or not a formula gets evaluated? Are cell formulas and their evaluated results not the preeminent use of the Calc program?
This is completely unrelated to “Excel superiority” (a good program BTW). The specific thing that you started to complain (applying formatting does not change data) works the same way in Calc and in Excel - if you have a formula-like text, it will stay text when you apply a numeric format. The way how Excel considers cell format at the entry time is also the same: when a cell is formatted as text, then anything you enter there, even when it looks like a formula, is stored as text (and does not need to be prepended with apostrophe). You only need an apostrophe when you are entering a formula-like text into a cell formatted as number.
It’s not “cell format controls if a formula is evaluated”. Rather, as shown and written above, cell format is taken into account when you are typing into a cell. People need an easy way to enter not only numeric data, but also texts, including those that might look like spreadsheet formulas. So it is a common spreadsheet convention that a cell formatted as text accepts everything typed into it as text. And then it’s not a formula, so nothing to evaluate, until you re-enter, allowing the program to re-interpret the data.
Sorry for my tone earlier. I am just frustrated about this same problem confronting me again since apparently I did not learn my lesson the first time I had to come here. I read through your comments twice, so let me know if I digested it correctly. Are you saying that I need to apply a numeric format to the cells in question before I enter the formula? (Also, what tool did you use to record your screen? I would like to demonstrate how to reproduce this issue and the counter measures I have taken that don’t work.)
Here’s a screenshot from my original formula entry (cell contents appear the same after pressing enter/selecting another cell):
@mikekaganski Now I’m doubly frustrated because I tried going through the motions on this page from top to bottom (how I resolved this issue earlier this week), but now I cannot seem to resolve it. Obviously I can manually just type the correct output in this case, but I want to learn “the” correct way so I can use LO for more complex tasks in the future. Here is the output when I use the functions in the following cells: