Syntax could be something like this: SUBSTITUTE("Text"; "SearchText"; "NewText"; Occurrence; Regular expressions) Text is the text 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 that is to replace the text segment. Occurrence (optional) indicates which occurrence of the search text is to be replaced. If this parameter is missing the search text is replaced throughout. Regular expressions (optional) indicates wether regular expressions are used or not. If this parameter is 1, regular expressions are allowed, when 0 or missing, regular expressions are not allowed. This makes it backwards compatible with the old behaviour. Example =SUBSTITUTE("123123123";"3";"abc") returns 12abc12abc12abc. =SUBSTITUTE("123123123";"3";"abc";2) returns 12312abc123. =SUBSTITUTE("123abc1.25w3";"^.*([:digit:]+)([,\.]{0,1})([:digit:]*).*$";"$1,$3";;1) returns 1,25 (with reservations for typos…). I think this would be a killer feature for some users.
I am not sure, but is this not already implemented with the existing function SUBSTITUTE (menu INSERT -> FUNCTION -> function SUBSTITUTE)?
Dear Bug Submitter, This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INVALID due to lack of needed information. For more information about our NEEDINFO policy please read the wiki located here: https://wiki.documentfoundation.org/QA/FDO/NEEDINFO If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed. Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team
> I am not sure, but is this not already implemented with the existing > function SUBSTITUTE (menu INSERT -> FUNCTION -> function SUBSTITUTE)? No, at least not according to Help. SUBSTITUTE exists, but it doesn't accept regular expressions, or at least Help doesn't mention it.
Dear Bug Submitter, Please read this message in its entirety before proceeding. Your bug report is being closed as INVALID due to inactivity and a lack of information which is needed in order to accurately reproduce and confirm the problem. We encourage you to retest your bug against the latest release. If the issue is still present in the latest stable release, we need the following information (please ignore any that you've already provided): a) Provide details of your system including your operating system and the latest version of LibreOffice that you have confirmed the bug to be present b) Provide easy to reproduce steps – the simpler the better c) Provide any test case(s) which will help us confirm the problem d) Provide screenshots of the problem if you think it might help e) Read all comments and provide any requested information Once all of this is done, please set the bug back to UNCONFIRMED and we will attempt to reproduce the issue. Please do not: a) respond via email b) update the version field in the bug or any of the other details on the top section of FDO
I also want to request that function SUBSTITUTE() can be used with regular expression or that a new function will be created for this functionality. I have seen that this was closed for inactivity and leak of information so here we go. For examples we will use the next basic table: A B --------------- 1 | Foo | | --------------- 2 | Boo | | --------------- 3 | Xoooo | | --------------- Note that I'm Spanish so you may see a little bad English :( so sorry in advance. Also will use regex as regular expressions, so it's a little sort. WHAT WE HAVE NOW ----- You can use SUBSTITUTE() function to search for a exact text and replace it. For example: B1 = SUBSTITUTE(A1;"Foo";"Cool") => Cool B2 = SUBSTITUTE(A2;"Foo";"Cool") => Boo This have limitations, you need to specify the search text exactly and many times it isn't know or have many variants to make a SUBSTITUTE() concatenation a doable option. Also we have many functions that accept regex but only to search, for example SEARCH() function, but with this we need to make very complex expressions. WHAT WE WANT TO HAVE ----- A thing that will solve this problem and add a great power to spreadsheets is the addition of regex to substitute function. We have two big alternatives, change SUBSTITUTE() to accept regular expressions or add a new function that accept regular expressions. * Edit SUBSTITUTE() to accept a parameter that enable regular expressions. For example something like `B2 = SUBSTITUTE(A2;".(oo)";"C\1l") => Boo` and `B2 = SUBSTITUTE(A2;".(oo)";"C\1l", 1) => Cool`. This make it compatible with old version. * Edit SUBSTITUTE() to accept regex by default. I think this will confuse the user and don't recommend it. * Add a new function, for example REGEX(text; regex; reemplacement). This (IMO) is the best option, no confuse and add new functionality. REGEX() function definition ------ REGEX(text; regex; replacement; [occurrences]) * text. A cell or text to search in it. * regex. A regular expression that must be matched. * replacement. A text (possibly with backtrack expression) to substitute the matched text. * occurrences. A optional number that indicate how many times you can apply the substitution. For example: B1 = SUBSTITUTE(A1;"([FfBb])(o+)";"\1\2l") => Fool B2 = SUBSTITUTE(A2;"([FfBb])(o+)";"\1\2l") => Bool B3 = SUBSTITUTE(A3;"([FfBb])(o+)";"\1\2l") => Xooool
Sorry in the last examples I have used SUBSTITUTE instead of REGEX. They must be: B1 = REGEX(A1;"([FfBb])(o+)";"\1\2l") => Fool B2 = REGEX(A2;"([FfBb])(o+)";"\1\2l") => Bool B3 = REGEX(A3;"([FfBb])(o+)";"\1\2l") => Xooool
Requesting expert advice - not sure if some open standard makes it so we can't do this
Ohh, so to do this it must be in the standard? It could be a very good addition to the standard if it isn't already part of it. But I don't know how this can be done.
(In reply to comment #4) > > I am not sure, but is this not already implemented with the existing > > function SUBSTITUTE (menu INSERT -> FUNCTION -> function SUBSTITUTE)? > > No, at least not according to Help. SUBSTITUTE exists, but it doesn't accept > regular expressions, or at least Help doesn't mention it. Hi Johnny, I just chatted w/a couple devs, and SUBSTITUTE does not currently support regexp. I'll leave this open as an enhancement request. Status -> NEW Whiteboard: (remove NeedAdvice)
This seems to basically be a kind of duplicate (not just a "see also" case) of bug#113977 which was resolved (concerning the relevant requested functionality) by adding the new function REGEX() available in V6.2 or higher. I would suggest QA to consider changing the state respectively. (Don't like to do it myself because I don't feel clearly entitled.)
Yes, I agree. The REGEX() function seems to do what I was looking for. Thanks!
There is now the REGEX function. https://help.libreoffice.org/6.3/en-US/text/scalc/01/func_regex.html?DbPAR=CALC#bm_id831542233029549