1

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










 ))))))))))))))))
1

=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

  • 1
    Hi User0, that works, and I tried modifying it a little for my purposes. I still have the problem that the length may be different between each string, so I tried...*shown in editted question* – Ceebee86 Jul 19 at 7:37
  • glad it helped you – user0 Jul 19 at 7:49

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.