Bug 158894

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