5

In Python 3, it is quite easy to extract text from named groups as shown in the following example:

import re
myStr = r"4000/2000/5000/7000"
reObj = re.compile(r"""(?P<g1>\d+)  # a capturing group named g1
                       /
                       (?P<g2>\d+)
                       /
                       (?P<g3>\d+)
                       /
                       (?P<g4>\d+)""", re.VERBOSE) 
matchObj = reObj.match(myStr)  # match the string to be searched
print(matchObj.group("g1"))  # 4000
print(matchObj.group("g2"))  # 2000 
print(matchObj.group("g3"))  # 5000 
print(matchObj.group("g4"))  # 7000

However, in LibreOffice Calc I simply couldn't get any clue (Calc does not even have a independent regex() function which gives a regex pattern). Position-based workarounds as in this post isn't what I need.

PLEASE GIVE ANSWERS INDEPENDENT OF POSITIONAL PARAMETERS and PLEASE EXEMPLIFY EXPLICITLY. e.g. MID() is not acceptable. Although the example given here is simple enough, but I need a general way to deal with a real situation that is far more complex.

2 Answers 2

11

In both Excel and Calc, the cleanest solution is to create a general-purpose regular expression macro. To do this in Calc, go to Tools -> Macros -> Organize Macros -> LibreOffice Basic and add the following code to Module1:

Function ReFind(findIn, patt, Optional group_param As Integer,  _
                Optional ignoreCase_param As Boolean)
    ' findIn - string or cell to search in
    ' patt - regexp string or cell containing regexp string
    ' group - which group to grab - analogy to \n in regexp syntax
    ' ignoreCase - false for case sensitive matches
    If IsMissing (group_param) Then
        group = 0
    Else
        group = group_param
    End If
    If IsMissing (ignoreCase_param) Then
        ignoreCase = False
    Else
        ignoreCase = ignoreCase_param
    End If
    oTextSearch = CreateUnoService("com.sun.star.util.TextSearch")
    oOptions = CreateUnoStruct("com.sun.star.util.SearchOptions")
    oOptions.algorithmType = com.sun.star.util.SearchAlgorithms.REGEXP
    If ignoreCase Then
        oOptions.transliterateFlags = _
            com.sun.star.i18n.TransliterationModules.IGNORE_CASE
    End If
    oOptions.searchString = patt
    oTextSearch.setOptions(oOptions)
    oFound = oTextSearch.searchForward(findIn, 0, Len(findIn))
    If oFound.subRegExpressions = 0 Then
        ReFind = "No results"
        MsgBox "No results"
        Exit Function
    ElseIf group >= oFound.subRegExpressions Then 
         ReFind = "No result for that group"
         MsgBox "No result for that group"
         Exit Function
    Else
         nStart = oFound.startOffset()
         nEnd = oFound.endOffset()
         ReFind = Mid(findIn, nStart(group) + 1, nEnd(group) - nStart(group))
    End If
End Function

Now you can use ReFind for any regular expressions needed in the spreadsheet. For example, in cell A1 enter 12345. In cell B1, enter the formula =REFIND($A$1,"(\d\d)(\d)",2). This will retrieve the third number, which is 3.

The code was adapted from https://forum.openoffice.org/en/forum/viewtopic.php?t=30502.

Note: It would be better to create the regular expression in python or java using an add-in. However that requires XML declaration files in an extension, which takes more time to set up.

3
  • 1
    Works like charm. Deserves 100+ rep. (This answer gives a general solution as well as an directly applicable example.)
    – Bill Huang
    Commented May 2, 2016 at 16:01
  • Nice! +1 - now, still deserves 90+ rep ;-)
    – tohuwawohu
    Commented May 6, 2016 at 9:08
  • This is wonderful, I can even use it in Writer. Now I just need to figure out how to do search and replace with on multiple matches.
    – Moss
    Commented Mar 10, 2020 at 5:42
4

AFAIK you can't use named groups in LO Calc Search/Replace or formulas, but you can use numerical references to pattern groups:

  • In the search field, you can use \1 to reference the first pattern group, \2 for the second and so on.
  • In the replace expression, use $1 as reference for the first search pattern group, $2 for the second and so on.

Search example

Assuming the four strings blue bluefish, black blackfish, blue blackfish and black bluefish, you can replace every string where the same color appears twice (strings 1 and 2) using the search pattern: (blue|black) \1fish. The \1 will reference the matching group, matching the whole string only if the color matched in the regex group (blue|black) appears before fish, too. (Example based on the OOo Wiki Documentation).

Replace Example

To transform the string 100/200/300/400 to 300/100/400/200 (with regex enabled in the search options), search for the pattern (\d+)/(\d+)/(\d+)/(\d+) and replace with $3/$1/$4/$2.

8
  • 1
    Pretty close but may you exemplify (in a directly applicable form)? I tried =SEARCH(A5, "(\d+)/(\d+)/(\d+)/(\d+)",1) on cell A5 containing 100/200/300/400 but got #VALUE!. I am new to LibreOffice. The section you gave wasn't enough for me to figure out the general logic. Thank you very much.
    – Bill Huang
    Commented May 2, 2016 at 9:34
  • The search pattern has to be the first argument, not the second. So, try =SEARCH("(\d+)/(\d+)/(\d+)/(\d+)",A5,1)
    – tohuwawohu
    Commented May 2, 2016 at 10:25
  • The above gives 1., not 100. =SEARCH("(\d+)/(\d+)/(\d+)/(\d+)",A5,\1) or =SEARCH("(\d+)/(\d+)/(\d+)/(\d+)",A5,'\1') also didn't work. Dollar sign also failed. (LibreOffice 5.0.5.2 @win10 x64)
    – Bill Huang
    Commented May 2, 2016 at 10:33
  • Yes, the result is correct. The SEARCH() function returns the position of the match. Since you're matching the complete string, beginning at 1, the result is the start of the string. The third argument is optional, adding an offset to begin the search in the middle of a string. It doesn't set a replacement. SEARCH() just checks for the position but doesn't replace anything.
    – tohuwawohu
    Commented May 2, 2016 at 10:39
  • So what code should I write, in order to have my cell B5 give 100, C5 give 200, D5 give 300 and E5 give 400?? May you please EXEMPLIFY EXPLICITLY (and edit in your post)? It is clearly not enough to find the beginning of the match.
    – Bill Huang
    Commented May 2, 2016 at 11:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .