Google Sheets – ArrayFormula to Extract Part of a String

formulasgoogle sheetsgoogle-sheets-arrayformularegexextract

I have come across the below formula, and I am finding it quite useful, I want to turn it into an array shortly, but before I do, I was wondering if there was an easier way to do this?

=if(len(LEFT(MID(A2,SEARCH("02",A2)+2,LEN(A2)),SEARCH("37",MID(A2,SEARCH("02",A2)+2,
 LEN(A2)))-1))=14,LEFT(MID(A2,SEARCH("02",A2)+2,LEN(A2)),SEARCH("37",MID(A2,SEARCH("02",A2)+2,
 LEN(A2)))-1),if(len(LEFT(MID(A2,SEARCH("02",A2)+2,LEN(A2)),SEARCH("15",MID(A2,SEARCH("02",A2)+2,
 LEN(A2)))-1))=14,LEFT(MID(A2,SEARCH("02",A2)+2,LEN(A2)),SEARCH("15",MID(A2,SEARCH("02",A2)+2,
 LEN(A2)))-1),if(len(LEFT(MID(A2,SEARCH("02",A2)+2,LEN(A2)),SEARCH("10",MID(A2,SEARCH("02",A2)+2,
 LEN(A2)))-1))=14,LEFT(MID(A2,SEARCH("02",A2)+2,LEN(A2)),SEARCH("10",MID(A2,SEARCH("02",A2)+2,
 LEN(A2)))-1),"")))

Google document to help illustrate: https://docs.google.com/spreadsheets/d/1JWHnZAw6NPg6P657q4ipD2aqyypq1bip8Bi_8JD-1w8/edit?usp=sharing

This code modified:

=ARRAYFORMULA(
 IF(LEN(IFERROR(REGEXEXTRACT(A2:A&"", "37(.*)02")))=(len(right(A2:A,((len(A2:A))-(find(37,A2:A)))-1))), 
                int((REGEXEXTRACT(A2:A&"", "37(.*)02"))),
 IF(LEN(IFERROR(REGEXEXTRACT(B2:B&"", "37(.*)02")))=(len(right(B2:B,((len(B2:B))-(find(37,B2:B)))-1))),
                int((REGEXEXTRACT(B2:B&"", "37(.*)02"))),
 IF(LEN(IFERROR(REGEXEXTRACT(C2:C&"", "37(.*)02")))=(len(right(C2:C,((len(C2:C,))-(find(37,C2:C,)))-1))),
                int((REGEXEXTRACT(C2:C&"", "37(.*)02"))),
IF(LEN(IFERROR(REGEXEXTRACT(A2:A&"", "37(.*)15")))=(len(right(A2:A,((len(A2:A))-(find(37,A2:A)))-1))), 
                int((REGEXEXTRACT(A2:A&"", "37(.*)15"))),
 IF(LEN(IFERROR(REGEXEXTRACT(B2:B&"", "37(.*)15")))=(len(right(B2:B,((len(B2:B))-(find(37,B2:B)))-1))),
                int((REGEXEXTRACT(B2:B&"", "37(.*)15"))),
 IF(LEN(IFERROR(REGEXEXTRACT(C2:C&"", "37(.*)15")))=(len(right(C2:C,((len(C2:C))-(find(37,C2:C)))-1))),
                int((REGEXEXTRACT(C2:C&"", "37(.*)15"))),
IF(LEN(IFERROR(REGEXEXTRACT(A2:A&"", "37(.*)10")))=(len(right(A2:A,((len(A2:A))-(find(37,A2:A)))-1))), 
                int((REGEXEXTRACT(A2:A&"", "37(.*)10"))),
 IF(LEN(IFERROR(REGEXEXTRACT(B2:B&"", "37(.*)10")))=(len(right(B2:B,((len(B2:B))-(find(37,B2:B)))-1))),
                int((REGEXEXTRACT(B2:B&"", "37(.*)10"))),
 IF(LEN(IFERROR(REGEXEXTRACT(C2:C&"", "37(.*)10")))=(len(right(C2:C,((len(C2:C))-(find(37,C2:C)))-1))),
                int((REGEXEXTRACT(C2:C&"", "37(.*)10"))),
IF(LEN(IFERROR(REGEXEXTRACT(A2:A&"", "37(.*)11")))=(len(right(A2:A,((len(A2:A))-(find(37,A2:A)))-1))), 
                int((REGEXEXTRACT(A2:A&"", "37(.*)11"))),
 IF(LEN(IFERROR(REGEXEXTRACT(B2:B&"", "37(.*)11")))=(len(right(B2:B,((len(B2:B))-(find(37,B2:B)))-1))),
                int((REGEXEXTRACT(B2:B&"", "37(.*)11"))),
 IF(LEN(IFERROR(REGEXEXTRACT(C2:C&"", "37(.*)11")))=(len(right(C2:C,((len(C2:C))-(find(37,C2:C)))-1))),
                int((REGEXEXTRACT(C2:C&"", "37(.*)11"))),
IF(LEN(IFERROR(REGEXEXTRACT(A2:A&"", "37(.*)")))=(len(right(A2:A,((len(A2:A))-(find(37,A2:A)))-1))), 
                int((REGEXEXTRACT(A2:A&"", "37(.*)"))),
 IF(LEN(IFERROR(REGEXEXTRACT(B2:B&"", "37(.*)")))=(len(right(B2:B,((len(B2:B))-(find(37,B2:B)))-1))),
                int((REGEXEXTRACT(B2:B&"", "37(.*)"))),
 IF(LEN(IFERROR(REGEXEXTRACT(C2:C&"", "37(.*)")))=(len(right(C2:C,((len(C2:C))-(find(37,C2:C)))-1))),
                int((REGEXEXTRACT(C2:C&"", "37(.*)"))),










 ))))))))))))))))

Best Answer

=ARRAYFORMULA(
 IF(LEN(IFERROR(REGEXEXTRACT(A2:A&"", "02(.*)37")))=14, 
                REGEXEXTRACT(A2:A&"", "02(.*)37"),
 IF(LEN(IFERROR(REGEXEXTRACT(B2:B&"", "02(.*)37")))=14,
                REGEXEXTRACT(B2:B&"", "02(.*)37"),
 IF(LEN(IFERROR(REGEXEXTRACT(C2:C&"", "02(.*)37")))=14,
                REGEXEXTRACT(C2:C&"", "02(.*)37"), ))))

0