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 [1] (including the brackets) in the "Search for" box. Enter [2] (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[1]" 3. Menu 'Edit -> Find + Replace', Insert "[1]" into "Find" and into 'Search for' and also 'Replace with' 4. <Replace all> Expected: Nothing happens Actual: "=RC[1]" will be replaced by "=rc[1]", Error 509 If you type "=rc[1]" to check validity of the formula, the formula will be modified to "=RC[1]" 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 [(Build ID:d337f79-a24c961-2865670-9752b71-7f8fd43 2fdd60d-fd28b6a-fd7bf20-aa369cb-28da3fb 6a9633a-931d089-ecd263f-c9b55e9-b31b807 82ff335-599f7e9-bc6a545-1926fdf)]" and with "LibreOffice Portable 3.3.3 - WIN7 Home Premium (64bit) German UI [OOO330m19 (Build:301 Tag 3.3.3.1)]". @James 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[1]" gets the aforementioned error if you substitute RC->rc (just like the previously reported substitution). Rather than treating "=rc[1]" 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[1]" into "=RC[1]", 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 @Kohei: 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: Find: Page2 Replace: Page3 6. Press Replace all Current behavior: #Name! The formula is =page3!B4 Expected behavior: 0 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 http://cgit.freedesktop.org/libreoffice/core/commit/?id=9ffe57dc020dc2658e2f326dc399fe0da0641050 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": https://git.libreoffice.org/core/+/58d3166152003f0c3fef329dce76e4aaa33fc289%5E%21 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: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
The test exist, set status to Verified.