I would like to be able to calculate the replacement text in a regex replacement using a LO Calc function which takes the extracted text as an argument (i.e. a back reference). Is this possible?
It would look something like this:
=REGEX(B12; "(FOO[1-9BAR]+)"; SOMEFUNCTION("$1"); "g")
Where SOMEFUNCTION() is any ordinary Calc function which can take a String argument (e.g. LOOKUP("$1"…) or VALUE("$1")) the argument to SOMEFUNCTION() is a back reference to the text extracted by the regex.
There is a solution of sorts in this post: Search a set of words in a string and replace any occurences with their corresponding definitions. However, this is very hard to extrapolate in a generic way as the text in between the regex targets is not arbitrary but is hard coded into the TEXTJOIN argument. It is also a bit ugly as it searches for a fixed number of occurrences and then ties itself in knots handling the errors for occurrences which are not there.
Note: edited to correct the use of Function rather than Formula
