Using REGEXEXTRACT in an array, searching multiple columns

Question

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.

player0 · Accepted Answer · 2019-07-20 22:36:34Z

1

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")))))

answered Jul 20, 2019 at 22:36

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

manc_stu Over a year ago

hoping you can take a look at this, following on from your above comment.... docs.google.com/spreadsheets/d/… - I'm having trouble with columns H-I and J-K. The dark yellow columns contain your suggestion above. But both yours and the original formula, are throwing up errors, or rather, not the result I was hoping for. I've added what i was expecting in cell A37 in order to keep this post to the point. Would really appreciate your help. Best regards, manc.

player0 Over a year ago

I1 cell:

={"10 BATCH / LOT # - n2+an..20";ARRAYFORMULA(REGEXREPLACE(IFERROR(IFERROR(IFERROR(IFERROR( REGEXEXTRACT(REGEXEXTRACT(A2:A&"", "^15(.+)"), "10(.{6,10})"),  REGEXEXTRACT(REGEXEXTRACT(B2:B&"", "^15(.+)"), "10(.{6,10})")),    REGEXEXTRACT(REGEXEXTRACT(C2:C&"", "^15(.+)"), "10(.{6,10})")))), "^10", ))}

Collectives™ on Stack Overflow

Using REGEXEXTRACT in an array, searching multiple columns

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related