In recent version (5.2.2.2) of Calc, if I enter the text of a function without the equals sign, e.g.
FIND(“Ach Deposit”;A18)
and then edit this cell by placing an = at the beginning, the result is
FIND()
and an Err:501. Typing exactly the same thing, i.e.
=FIND(“Ach Deposit”;A18)
gives a valid response (the number 1).
This was not previously the case (I have used Find and Replace to remove the equals sign so that a formula could be copied elsewhere without changing the cell reference, and then putting the equals sign back), and makes no sense to me. What is wrong?
Perhaps the problem is in quotes. While the formula is entered as text Calc applies AutoCorrect to quotes - https://help.libreoffice.org/5.2/Common/Localized_Options#Single_quotes_.2F_Double_quotes And after conversion into the formula does not understand what kind of quotes. IMHO!
I tried to repeat this by removing the ‘=’, then copied the cell and pasted to a new cell, then edit the new cell to add ‘=’, but this was no problem. Also using 5.2.2.2. Perhaps I have different option set for autocorrect.
FIND(“Ach Deposit”;A18) and FIND("Ach Deposit";A18) are not exactly the same. The first has different characters for quote than the second.
If I copy the first into a calc cell then add the ‘=’ I get an error 501.
If I copy the second into a calc cell and then add the ‘=’ then it works.
You may have copied the first example from some source other than Calc, maybe a web page.
If the ‘Replace double quote’ (with “typographic” quotes as defined for the locale is chosen under ‘Autocorrect options’ LibO will do so when the supposed formula is entered as a text. Trying later to make it a formula will not revert this. “typographic” quotes, on the other hand, are not recognised inside formulae. Unfortunately the option cannot be set differently for ‘Writer’ and for ‘Calc’. Due to a leading “=” the cell editor will know the intention and omit the replacement.
Thank you to both; I was not sensitive to this difference.