Download it now!
Bug 39917 - EDITING Find/Replace modifies formula in R1C1 syntax to invalid lowercase
Summary: EDITING Find/Replace modifies formula in R1C1 syntax to invalid lowercase
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.4.2 release
Hardware: Other All
: medium normal
Assignee: Kohei Yoshida
Whiteboard: target:3.5 target:6.3.0
Depends on:
Reported: 2011-08-07 21:41 UTC by James
Modified: 2019-12-07 15:25 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

Sample to demonstrate bug (7.13 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-08-07 21:41 UTC, James

Note You need to log in before you can comment on or make changes to this bug.
Description James 2011-08-07 21:41:00 UTC
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.
Comment 1 Rainer Bielefeld Retired 2011-08-07 23:09:34 UTC
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 <> 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


with "LibreOffice Portable 3.3.3  - WIN7  Home Premium (64bit) German UI [OOO330m19 (Build:301  Tag]". 

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?
Comment 2 James 2011-08-08 04:13:58 UTC
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).
Comment 3 Rainer Bielefeld Retired 2011-08-08 05:22:45 UTC
(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.
Comment 4 Viktor Mileikovskyi 2011-11-10 09:03:29 UTC
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:
The formula is =page3!B4

Expected behavior:
The formula is =Page3!B4
Please help me because R1C1 is my favorite style.
Comment 5 Kohei Yoshida 2011-11-30 16:36:21 UTC
Adding this to my TODO list for 3.5.
Comment 6 Kohei Yoshida 2011-12-13 07:39:29 UTC
I figured it out.  It was a silly oversight.
Comment 7 Kohei Yoshida 2011-12-13 07:53:28 UTC
Fixed on master

and cherry-picked to the libreoffice-3-5 branch.  The fix will be in Beta2.
Comment 8 Commit Notification 2018-12-13 06:24:43 UTC
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 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.
Comment 9 raal 2019-12-07 15:25:19 UTC
The test exist, set status to Verified.