SUBSTITUTE("Text"; "SearchText"; "NewText"; Occurrence) Text is the text string in which text segments are to be exchanged. SearchText is the text segment that is to be replaced (a number of times). NewText is the text string that is to replace the text segment. Occurrence (optional) indicates which occurrence of the search text is to be replaced. If this parameter is omitted the search text is replaced throughout. Enhancement requested: *** If SearchText and NewText are both arrays, they must be the same length: then each array element of NewText replaces the corresponding element of SearchText. If only SearchText is an array, NewText replaces each element of SearchText. *** This enhancement is compatible with existing SUBSTITUTE.

This one is trickier, please provide a simple example of how this would ever be useful. It seems like almost no one would find real world cases to use this. Marking as NEEDINFO - once you clarify please mark as UNCONFIRMED.

My spreadsheets have frequent instances of code like: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2,MID(G2,1,1),"",1),MID(G2,2,1),"",1),MID(G2,3,1),"",1),MID(G2,4,1),"",1),MID(G2,5,1),"",1),MID(G2,6,1),"",1),MID(G2,7,1),"",1),MID(G2,8,1),"",1),MID(G2,9,1),"",1),MID(G2,10,1),"",1),MID(G2,11,1),"",1),MID(G2,12,1),"",1),MID(G2,13,1),"",1),MID(G2,14,1),"",1),MID(G2,15,1),"",1) ... and the formula would be longer if it were legal to make it so. My suggestion for enhancement would reduce this to a single call to substitute. This code is calculating {E2}-{G2}, where {X} is the contents of cell X regarded aa a set.

(In reply to David Lynch from comment #2) > My spreadsheets have frequent instances of code like: > > =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE > (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE > (SUBSTITUTE(E2,MID(G2,1,1),"",1),MID(G2,2,1),"",1),MID(G2,3,1),"",1),MID(G2, > 4,1),"",1),MID(G2,5,1),"",1),MID(G2,6,1),"",1),MID(G2,7,1),"",1),MID(G2,8,1), > "",1),MID(G2,9,1),"",1),MID(G2,10,1),"",1),MID(G2,11,1),"",1),MID(G2,12,1), > "",1),MID(G2,13,1),"",1),MID(G2,14,1),"",1),MID(G2,15,1),"",1) > > ... and the formula would be longer if it were legal to make it so. > > My suggestion for enhancement would reduce this to a single call to > substitute. > > This code is calculating {E2}-{G2}, where {X} is the contents of cell X > regarded aa a set. This is obviously an extreme cornercase and probably not a good use of a spreadsheet application to begin with. Not only would would this be a significant hack of the code requiring someone with a high level of skills, it would also cause potential nightmares for interop. Finally, I suspect this would help all of 1 person ever. We could mark it as NEW and leave it there indefinitely which used to be the protocol for these kinds of requests. My instinct is to mark this as WONTFIX but I'll leave someone else to do that.

Thanks for considering this: I'm content with WONTFIX.

This is nothing for SUBSTITUTE as SUBSTITUTE is defined to take scalar values as arguments, which is implemented by various spreadsheet applications, and thus in array context already acts differently than what you propose. It could be only implemented as a new function. I'm not sure I understand the use case though. You want an array (or range reference for that matter) of SearchText be searched subsequently in Text from position Occurrence, and then for a match replace it with the corresponding element of NewText and then continue with the next SearchText from the same Occurrence position (or even a different one if Occurrence was a range/array as well) and the result should be a single text string, not an array? Did I get that right? That's quite a specialized use case..

(In reply to Eike Rathke from comment #5) > I'm not sure I understand the use case though. You want an array (or range > reference for that matter) of SearchText be searched subsequently in Text > from position Occurrence, and then for a match replace it with the > corresponding element of NewText and then continue with the next SearchText > from the same Occurrence position (or even a different one if Occurrence was > a range/array as well) and the result should be a single text string, not an > array? Did I get that right? That's quite a specialized use case.. Set to NEEDINFO. David: change back to UNCONFIRMED after you have provided the information.

Text is never an array. Occurrence is never an array. The function should be implemented as: For each element SearchText[i] ST = SearchText[i] if NewText is an array NT = NewText[i] else NT = NewText end if existing_SUBSTITUTE(Text,ST,NT,Occurrence) end for In Comment 5, Eike writes: "This is nothing for SUBSTITUTE as SUBSTITUTE is defined to take scalar values as arguments, which is implemented by various spreadsheet applications, and thus in array context already acts differently than what you propose." I don't understand this. Other Libreoffice functions, such as ROW and COLUMN, can take either scalar or array arguments and behave appropriately.

This has been 1.5 years and we had an expert put in their thoughts. Although there may be an EXTREME corner case where a new function would be appropriate, it seems like it would potentially be for a single user and I would say this will never be implemented. Closing as WONTFIX. @David - thank you for taking the time to report. With tens of millions of users, sometimes pet enhancements just can't be implemented. Thank you for your understanding.

(In reply to David Lynch from comment #7) > I don't understand this. Other Libreoffice functions, such as ROW and > COLUMN, can take either scalar or array arguments and behave appropriately. Fwiw, SUBSTITUTE also behaves appropriately, =SUBSTITUTE("abcde",{"a";"b"},{"x";"y"},1) as array formula returns an array of two elements {"xbcde";"aycde"} where in each iteration for Text the first occurrence of SearchText[i] is replaced with NewText[i] to form a result element. This is the same behaviour as for other functions and operators that take scalar arguments, i.e. iterate over arguments arrays and return a result array, it just is not what you desired it did in your special case. For your example of ROW, =ROW(A11:A13) in array evaluation returns an array of 3 elements {11;12;13} which is not different behaviour. Or =CONCATENATE({"a";"b"},{"x";"y"}) => {"ax";"by"}

In reply to Joel: as I said in cooment 4, I'm happy with WONTFIX. In reply to Eike, I agree with your comment, in your example, I want "xycde" not {"xbcde";"aycde"}