Search code examples
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasre2

Using REGEXEXTRACT in an array, searching multiple columns

Can someone please tell me what I am doing wrong in this formula?

=ARRAYFORMULA(REGEXEXTRACT((A2:A&"")+(B2:B&"")+(C2:C&"")), "02(\d{14})37")

I'm trying to extract a 14 digit number that sits between 02 and 37 that may be in columnA, columnB or columnC.

I've tried this also, with the expected result showing on the first row only:

=ARRAYFORMULA(REGEXEXTRACT(textjoin(" ",true,A2:C),"02(\d{6,14})37"))

I'm really confuzzled.

Solution

  • it needs to be like this:

    =ARRAYFORMULA(IFERROR(IFERROR(IFERROR(IFERROR(
 REGEXEXTRACT(A2:A&"", "02(\d{14})37"), 
 REGEXEXTRACT(B2:B&"", "02(\d{14})37")),   
 REGEXEXTRACT(C2:C&"", "02(\d{14})37")))))