1

I have a page which contains a list of regular expressions in the format "some text (thing I want) but only if (thing is matched here)" that are fairly varied. There are 29 of them so far, and I'd prefer not to put them into a single formula, though that is my fallback. A sample of what I'm doing is here.

I have tried several techniques, for example I used textjoin() to concatenate all my conditions and am able to correctly get a match - that is just the true/false that this is valid - but I am unable to then perform the corresponding extract because I don't know what row I've matched on. I thought this would be the best way to go, but other formulas like VLOOKUP can't be used with a regular expression so I'm uncertain how to obtain that data.

The closest I've gotten is shown here, that returns the thing I want but the other groups as well.

=textjoin("",true,arrayformula(if(iserror(REGEXEXTRACT(E2,'Criteria'!B2:B)),"",choose(1,REGEXEXTRACT(E2,'Criteria'!B2:B)))))

I'm using textjoin so that the result isn't overwritten by "" by non matches on other lines and my expectation was that choose would restrict the textjoin only to the first element but this is not the output I'm seeing.

Thoughts on how to extract only the pattern match for "thing I want"?

Improve this question
4
  • 2
    Welcome. Please read how to and share a test sheet so as you can be easier helped.
    – marikamitsos
    Jul 19, 2020 at 5:02
  • Added the relevant test sheet that demonstrates the incorrect behaviour.
    – Stephen
    Jul 19, 2020 at 5:19
  • Welcome. Would you clarify how many rows of data you are working with, and also clarify whether this is a repetitive task, or a one-off exercise.
    – Tedinoz
    Jul 19, 2020 at 5:56
  • There are about 29 regex (Criteria tab), and a minimum of several hundred data elements. This will be an ongoing task that several people will be working on.
    – Stephen
    Jul 19, 2020 at 14:19

2 Answers 2

Reset to default
2

You would need to alter your "regular expression" in the sample regex

from: ((?:[[:alpha:]]+\s?)+)
to: (?:[[:alpha:]]+\s?)+

Following that, your formula will work just fine.

=textjoin("",true,arrayformula(if(iserror(REGEXEXTRACT(B2,Criteria!$A$2:$A)),"",choose(1,REGEXEXTRACT(B2,Criteria!$A$2:$A)))))

enter image description here

You could also use [A-Za-z] instead of [:alpha:] as shown in cell C2.

BUT
If you also change your formula and use the Arrayformula function in a different way, you can use just one formula for all rows.

=ArrayFormula(IFERROR(REGEXEXTRACT(B2:B,Criteria!$A$2:$A)))

enter image description here

Improve this answer
4
  • Removing the capturing group from around the criteria is not preferred, I need that information in a separate cell, and though I could create a 2nd regex for that if absolutely necessary but it's not preferred. The data is always non-numeric for both capturing groups. Yes A-Za-z would work fine, I'm not sure that's causing any error however.
    – Stephen
    Jul 19, 2020 at 14:24
  • So you want both the interesting item as well as a criteria in your results?
    – marikamitsos
    Jul 19, 2020 at 14:38
  • In a different column, yes. The use of a second capturing group was intentional. I assumed that a solution to the simplified problem would explain why choose is not functioning, which I could then apply to both situations.
    – Stephen
    Jul 19, 2020 at 14:39
  • 1
    let's meet at your sheet
    – marikamitsos
    Jul 19, 2020 at 14:40
0

The problem with the formula is the usual problem of not being careful about data types: CHOOSE() requires a list as inputs. The return value of REGEXEXTRACT() is a single object that contains a row of cells. To pick an individual cell from a row one must use INDEX().

However, the result of an ARRAYFORMULA of REGEXEXTRACTs is multiple rows and while INDEX() can pick a row and column, it is not obvious which is the correct one to pick. This is for the same reason we can't use a LOOKUP(), we don't know which regular expression matched.

To flatten the structure we can use TEXTJOIN() with a delimiter and importantly with the ignore empty flag set to true. The cells that were generated when REGEXEXTRACT did not match are removed, and we are left with a row that contains only the matching cells.

Since we are back to a single row, using INDEX() is practical again.

=index(split(textjoin("|",true,arrayformula(iferror(REGEXEXTRACT(E3,Criteria!$A$2:$A),""))),"|"),0,1)

The final mess incorporates the suggestion from @marikamitsos to use iferror instead of if(iserror())

I think this pattern may be useful to other searchers, and a reminder to myself to be more mindful of return types! :)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.