0

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(.*)"), ))))))))))))))))
0

=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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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