Created attachment 50021 [details]
Sample to demonstrate bug
Open up the attached spreadsheet. It has only 3 populated cells.
Bring up the Find & Replace menu.
Enter  (including the brackets) in the "Search for" box.
Enter  (including the brackets) in the "Replaced with" box.
Click Replace All.
You will get an Error 509 "Operator missing" error in R1C1. If you inspect R1C1, it obviously isn't missing anything. It's a completely valid formula. To clear the error, you must manually edit the cell and replace any character with itself (and hit return).
Since the edited formula will be accepted if LO thinks you have manually entered it, thee is obviously some problem in how LO simulates a cell being entered after it has been edited by Find & Replace.
I can reproduce the effect ("Error 509") with reporter's sample and "LibreOffice 3.4.2 - WIN7 Home Premium (64bit) German UI [OOO340m1 (Build:203)]" , but I do not understand all details of the step by step instruction:
- IMHO before "Click Replace All" a hint to activate Regular expressions is
missing or a hint that Syntax "Excel R1C1" has to be selected (or whatever else)
For me this R1C1 syntax is very unusual. Is it important?
I do not understand the sense of that find and replace. What is expected?
With property 'Syntax "Excel R1C1"' it seems I can confirm the problem. IMHO a more easy way to reproduce the problem is as following:
0. Open reporter's sample
1. Menu 'Tools -> Options -> Calc -> Formulas - Formula options -
Syntax = "Excel R1C1"
2. Check cell Column 1 Row 1, Contents should be "=RC"
3. Menu 'Edit -> Find + Replace', Insert "" into "Find" and into 'Search for'
and also 'Replace with'
4. <Replace all>
Expected: Nothing happens
Actual: "=RC" will be replaced by "=rc", Error 509
If you type "=rc" to check validity of the formula, the formula will be modified to "=RC" with <Enter>
So I believe the error message is correct, but the question is why find & Replace creates the invalid formula.
You get the same strange result when you Find / Replace "[" by "[" or "r" by "r", ...
I am pretty sure that I saw a similar problem some time ago
Sounds similar to
"Bug 35020 - Find & Replace changes case of sheet name in formulas Windows XP"
I believe I also saw a bug for such a case problem for formulas, but
currently I can't find it.
"Bug 39915 - Defining a named range is case sensitive, but entering it in a formula is not"
Also in <https://bugs.freedesktop.org/show_bug.cgi?id=35329#c2> I see such an uppercase lowercase problem, but that might be something different.
I also see the problem with Master "LibO-dev 3.4.5 – WIN7 Home Premium (64bit) English UI
with "LibreOffice Portable 3.3.3 - WIN7 Home Premium (64bit) German UI [OOO330m19 (Build:301 Tag 126.96.36.199)]".
Can you please number steps in your instructions, that would ease references.
Can you confirm my observations and conclusions? Did I recognize the core of your problem?
I don't believe R1C1 syntax is important. However, I see now that R1C1 is what triggers the problem.
If you're using A1 syntax, and a cell contains the formula "=B1", a Find & Replace of B1->c1 (note lowercase) doesn't produce a bad formula. LO correctly treats the replaced formula of "=c1" as if it were newly entered, creating the formula "=C1".
If you're using R1C1 the formula "=RC" gets the aforementioned error if you substitute RC->rc (just like the previously reported substitution). Rather than treating "=rc" as a newly entered formula, LO treats it as a bad cell reference. If it were treated as A1 syntax is treated that error wouldn't happen, the formula parser would turn "=rc" into "=RC", which is a good cell reference.
Bug 39915 is unrelated to this bug.
I didn't see any regular expression hints, so far as I know regular expressions have nothing to do with this bug (though I do have them turned on).
(In reply to comment #2)
> so far as I know regular expressions
> have nothing to do with this bug (though I do have them turned on).
I think so, too. But with my preselected "Calc A1" syntax I only received a message with RegEx allowed.
Your resting comment shows that we agree concerning the problem, so CONFIRMED
All OS because I reproduced with WIN 7
Modify Version due to Comment 1
Please feel free to reassign if it’s not your area. Please set Status to ASSIGNED if you accept this Bug.
LibreOffice 3.4.3 and 3.4.4 RC2 have the same problem Using Excel A1 and Excel R1C1 modes:
1. Create a workbook with 3 sheets: Page1, Page2, Page3.
2. Tools -> Options -> LibreOffice Calc -> Formula: Set syntax to Excel A1
3. Type in (for example) B4: =Page2!B4
4. Edit -> Find and Replace
5. Fill fields:
6. Press Replace all
The formula is =page3!B4
The formula is =Page3!B4
Please help me because R1C1 is my favorite style.
Adding this to my TODO list for 3.5.
I figured it out. It was a silly oversight.
Fixed on master
and cherry-picked to the libreoffice-3-5 branch. The fix will be in Beta2.
Zdeněk Crhonek committed a patch related to this issue.
It has been pushed to "master":
uitest for bug tdf#39917
It will be available in 6.3.0.
The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
Affected users are encouraged to test the fix and report feedback.