https://bugs.documentfoundation.org/show_bug.cgi?id=158894
Bug ID: 158894
Summary: UI: Find & Replace does not create array formulas
Product: LibreOffice
Version: 6.4.7.2 release
Hardware: x86-64 (AMD64)
OS: Linux (All)
Status: UNCONFIRMED
Severity: enhancement
Priority: medium
Component: Calc
Assignee: libreoffice-bugs@lists.freedesktop.org
Reporter: devona...@gmail.com
Description:
Find & Replace does not have a way to change the cell type from a normal
formula to an array formula. It works with cells that are already designated
as array formulas (but see also:
https://bugs.documentfoundation.org/show_bug.cgi?id=130625 ), but it will not
replace a non-array formula with a working array formula.
Steps to Reproduce:
1. Create a spreadsheet with one or more formulae that need to be replaced (my
example is =SUMIF(B6:B125,"<>#N/A",B6:B125)
2. Open Find & Replace (Ctrl-H) and search for the formula (Example with a
regex: ^=SUMIF\((.+),\"<>#N/A\".+ )
3. Enter an array formula in the replace box, using {} to denote the array
formula (Example that works with regex above: {=SUM(IFERROR($1,""))} )
4. Use 'replace' or 'replace all' to replace the original formula
Actual Results:
Formula is replaced with the string-literal in the replace box, but no array
formula is created. (i.e. using the example provided above, the cell contains
{=SUM(IFERROR(B6:B125,""))} as a string, not an array formula)
Expected Results:
Formula is replaced with an array formula. (I.e. using the example provided
above, the cell should contain =SUM(IFERROR(B6:B125,"")) and it should be a
valid array formula).
Reproducible: Always
User Profile Reset: No
Additional Info:
This happens because Find & Replace operates on the representation of the
formula, not the formula itself, and there is no way to request that the
results be entered as an array formula (i.e. there's no way to execute
"replace" using the equivalent of Ctrl-Shift-Enter, which is the only way to
create an array formula normally). Perhaps if it is not desireable to use {}
as an operator to create an array formula (since {} is just a visual reminder
that a cell contains an array formula, not the way an array formula is actually
created), there should be an option below the replace box that specifies
whether the replace box will create an array? This seems clumsy to me, but is
probably the least-error prone way to solve this issue.
In addition, searching returns results based on the visual representation of
the formula, not the formula itself, so a search term that is enclosed with {}
will return both array formulae and string literals that are enclosed with {}.
I.e. the search term {=SUM(IFERROR(B6:B125,""))} matches both the array formula
=SUM(IFERROR(B6:B125,"")) and the string literal {=SUM(IFERROR(B6:B125,""))} .
A replace operation based on this search criterion will replace array formulae
with array formulae, and string literals with string literals, but will not
change one type of formula to another.
A more intuitive way to solve this would be to assume that a replacement
formula enclosed with {} is always intended to replace the search string with
array formulae, with the obvious issue that string literals might be
erroneously converted to array formulae (but, shouldn't most string literals be
assumed to start with ' ?)
See further discussion of this issue on the ask site:
https://ask.libreoffice.org/t/find-replace-with-matrix-formula/99071
--
You are receiving this mail because:
You are the assignee for the bug.
