Google-sheets – How to partial extract from 3 columns with multiple levels of rules

formulasgoogle sheetsgoogle-sheets-arrayformularegexextract

The formula someone on this forum provided worked well, but I have run into a problem, which I am struggling to get around.

Google sheets Link

The formula in H2 looks between 00 & 02 and displays the result.

H12 will not display anything, due to the code in B12 having 2 x '02's in the code. If I change the 2nd occurring '02' to '03' the formula works. If I amend a random number after '35' to '02' the formula still works. I only get the problem if the 2nd '02' is after the first one. Is there any way to amend this formula to ignore the 2nd answer after it gets the first one? (I hope I made sense)

=ARRAYFORMULA(
 IF(LEN(IFERROR(REGEXEXTRACT(A2:A&"", "00(.*)02")))=18, 
                REGEXEXTRACT(A2:A&"", "00(.*)02"),
 IF(LEN(IFERROR(REGEXEXTRACT(B2:B&"", "00(.*)02")))=18,
                REGEXEXTRACT(B2:B&"", "00(.*)02"),
 IF(LEN(IFERROR(REGEXEXTRACT(C2:C&"", "00(.*)02")))=18,
                REGEXEXTRACT(C2:C&"", "00(.*)02"),
IF(LEN(IFERROR(REGEXEXTRACT(A2:A&"", "00(.*)15")))=18, 
                REGEXEXTRACT(A2:A&"", "00(.*)15"),
 IF(LEN(IFERROR(REGEXEXTRACT(B2:B&"", "00(.*)15")))=18,
                REGEXEXTRACT(B2:B&"", "00(.*)15"),
 IF(LEN(IFERROR(REGEXEXTRACT(C2:C&"", "00(.*)15")))=18,
                REGEXEXTRACT(C2:C&"", "00(.*)15"),
IF(LEN(IFERROR(REGEXEXTRACT(A2:A&"", "00(.*)10")))=18, 
                REGEXEXTRACT(A2:A&"", "00(.*)10"),
 IF(LEN(IFERROR(REGEXEXTRACT(B2:B&"", "00(.*)10")))=18,
                REGEXEXTRACT(B2:B&"", "00(.*)10"),
 IF(LEN(IFERROR(REGEXEXTRACT(C2:C&"", "00(.*)10")))=18,
                REGEXEXTRACT(C2:C&"", "00(.*)10"),
IF(LEN(IFERROR(REGEXEXTRACT(A2:A&"", "00(.*)11")))=18, 
                REGEXEXTRACT(A2:A&"", "00(.*)11"),
 IF(LEN(IFERROR(REGEXEXTRACT(B2:B&"", "00(.*)11")))=18,
                REGEXEXTRACT(B2:B&"", "00(.*)11"),
 IF(LEN(IFERROR(REGEXEXTRACT(C2:C&"", "00(.*)11")))=18,
                REGEXEXTRACT(C2:C&"", "00(.*)11"),
IF(LEN(IFERROR(REGEXEXTRACT(A2:A&"", "00(.*)")))=18, 
                REGEXEXTRACT(A2:A&"", "00(.*)"),
 IF(LEN(IFERROR(REGEXEXTRACT(B2:B&"", "00(.*)")))=18,
                REGEXEXTRACT(B2:B&"", "00(.*)"),
 IF(LEN(IFERROR(REGEXEXTRACT(C2:C&"", "00(.*)")))=18,
                REGEXEXTRACT(C2:C&"", "00(.*)"), ))))))))))))))))

Best Answer

=ARRAYFORMULA(IFERROR(
 IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(
 IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(
 REGEXEXTRACT(A2:A&"", "00(\d{18})02"), 
 REGEXEXTRACT(B2:B&"", "00(\d{18})02")), 
 REGEXEXTRACT(C2:C&"", "00(\d{18})02")), 
 REGEXEXTRACT(A2:A&"", "00(\d{18})15")), 
 REGEXEXTRACT(B2:B&"", "00(\d{18})15")), 
 REGEXEXTRACT(C2:C&"", "00(\d{18})15")), 
 REGEXEXTRACT(A2:A&"", "00(\d{18})10")), 
 REGEXEXTRACT(B2:B&"", "00(\d{18})10")), 
 REGEXEXTRACT(C2:C&"", "00(\d{18})10")), 
 REGEXEXTRACT(A2:A&"", "00(\d{18})11")), 
 REGEXEXTRACT(B2:B&"", "00(\d{18})11")), 
 REGEXEXTRACT(C2:C&"", "00(\d{18})11")), 
 REGEXEXTRACT(C2:C&"", "00(\d{18})")), 
 REGEXEXTRACT(B2:B&"", "00(\d{18})")), 
 REGEXEXTRACT(A2:A&"", "00(\d{18})"))))

0

Related Topic