Download it now!
Bug 107369 - Enhancement to Calc function SUBSTITUTE
Summary: Enhancement to Calc function SUBSTITUTE
Status: RESOLVED WONTFIX
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.2.3.3 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2017-04-23 14:02 UTC by David Lynch
Modified: 2018-11-20 14:52 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description David Lynch 2017-04-23 14:02:01 UTC
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.
Comment 1 Joel Madero 2017-04-23 15:04:16 UTC
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.
Comment 2 David Lynch 2017-04-23 15:56:50 UTC
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.
Comment 3 Joel Madero 2017-04-23 19:09:16 UTC
(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.
Comment 4 David Lynch 2017-04-23 19:22:51 UTC
Thanks for considering this: I'm content with WONTFIX.
Comment 5 Eike Rathke 2017-04-28 18:50:17 UTC
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..
Comment 6 Buovjaga 2017-08-15 10:16:18 UTC
(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.
Comment 7 David Lynch 2017-08-23 08:49:14 UTC
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.
Comment 8 Joel Madero 2018-11-20 12:09:51 UTC
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.
Comment 9 Eike Rathke 2018-11-20 13:48:42 UTC
(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"}
Comment 10 David Lynch 2018-11-20 14:52:10 UTC
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"}