Bug Hunting Session
Bug 60183 - Feature request: Allow regular expressions in cell functions like SUBSTITUTE()
Summary: Feature request: Allow regular expressions in cell functions like SUBSTITUTE()
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2013-02-02 11:50 UTC by Johnny Rosenberg
Modified: 2019-08-08 14:53 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 Johnny Rosenberg 2013-02-02 11:50:02 UTC

    
Comment 1 Johnny Rosenberg 2013-02-02 12:05:51 UTC
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.
Comment 2 A (Andy) 2013-03-29 14:59:36 UTC
I am not sure, but is this not already implemented with the existing function SUBSTITUTE (menu INSERT -> FUNCTION -> function SUBSTITUTE)?
Comment 3 QA Administrators 2013-09-24 01:43:53 UTC Comment hidden (obsolete)
Comment 4 Johnny Rosenberg 2013-09-24 17:45:42 UTC
> 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.
Comment 5 QA Administrators 2013-10-25 15:16:43 UTC Comment hidden (obsolete)
Comment 6 Javier Alfonso 2013-12-10 12:08:25 UTC
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
Comment 7 Javier Alfonso 2013-12-10 12:10:22 UTC
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
Comment 8 Joel Madero 2013-12-22 16:45:02 UTC
Requesting expert advice - not sure if some open standard makes it so we can't do this
Comment 9 Javier Alfonso 2013-12-23 09:56:12 UTC
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.
Comment 10 Robinson Tryon (qubit) 2014-02-04 12:59:07 UTC
(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)
Comment 11 Wolfgang Jäger 2019-08-08 09:11:54 UTC
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.)
Comment 12 Johnny Rosenberg 2019-08-08 14:53:07 UTC
Yes, I agree. The REGEX() function seems to do what I was looking for.
Thanks!