libreoffice-calc
Consider some libreoffice-calc function called MYFUNC which takes a cell range for example A2:B40 as an argument, so I can call MYFUNC(A2:B40).
Now I want to be able to write A2:B40 as text into another cell say D4 and give the cell range defined in D4 as an argument to MYFUNC. I.e. I want to be able to write something like this MYFUNC(GETCELLRANGEFROM(D4) which should be equivalent to MYFUNC(A2:B40).
Just execute a regex search: In the Find & Replace dialogue, open Other Options... and enable Regular Expressions. Then, in the search field, enter .*House.*; replace with Car. Now, since the regex pattern matches the whole cell content containing House, the complete cell content is replaced by Car.
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.
Best Answer
Replacing
GETCELLRANGEFROM(D4)byINDIRECT(D4)should do what you want to achieve (given that D4 contains the stringA2:B40). The INDIRECT function "translates" a string into a reference.Here's a short example how it works:
stringvalue, representating a reference to column A;=SUM(INDIRECT(B1))So, C1 calculates a sum, based on the reference string in B1. Modifying B1 to
A1:A7will change C1, too (resulting in 28). This way, INDIRECT "translates" a string to a reference that can be used in another (surrounding) formula.