How Can I make an Arrayformula of an already complex Arrayformula?

Question

This type of question has been asked many times on here (I know), and I've tried to translate half a dozen answers into my particular formula without success (like the query smush header trick). I guess I don't know how or where to implement it into my already pretty complex formula (which parses a multi-line block of opening hours text into a single start/end time format for each day of the week (and secondary times if present). All of my attempts either don't work, or the result of the calculation of the first row is repeated down the entire column.

I've backed all my attempts up, and will share the base formula that I'd like to ultimately be "arrayformula'd" down the entire column (for thousands of rows).

Here's the Google Sheet

And here's the base formula thus far:

=if(isblank($A3:$A),,iferror(
  regexreplace(
    concatenate(
      arrayformula(
        text(
          split(
            INDEX(
              REGEXEXTRACT(
                regexreplace($A3:$A,"–","-"),
                "(\s?" & B$1 & ":\s)((\d?\d:\d\d\s[AMP]*\s?\W\s\d?\d:\d\d\s[AMP]{2})|(Closed)|(Open\s24\shours))"
              ),1,2 
            ),"-",true,true
          ),"HH:MM-"
        )
      )
    ),"(\d\d:\d\d\-\d\d:\d\d)\-","$1"
  ),
))

Thanks in advance for your help.

EDIT: I've been asked to clarify what the formula is doing. The formula is parsing a block of text which contains the business hours of a business from Monday to Sunday into a machine readable format (which I will be importing into a database). If the business has blocks of time (like a restaurant that opens for lunch, has a break, and then reopens for dinner), the formula in the adjacent cell accounts for that too).

Answer 1

try:

=ARRAYFORMULA(IFERROR(HLOOKUP(B2:O2, QUERY(SPLIT(FLATTEN(IF(IFERROR(
 SPLIT(INDEX(SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))), ": ", ),,2), ",;"))<>"", 
 COUNTIFS(
 INDEX(SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))), ": ", ),,1), 
 INDEX(SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))), ": ", ),,1), 
      ROW(INDIRECT("A1:A"&COUNTA(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))))),
 "<="&ROW(INDIRECT("A1:A"&COUNTA(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))))))&"♣"&LOWER(
  LEFT(INDEX(SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))), ": ", ),,1), 3))&COLUMN(A:B)&"♣"&
 SUBSTITUTE(SUBSTITUTE({TRANSPOSE(QUERY(TRANSPOSE(TEXT(SPLIT(INDEX(SPLIT(INDEX(
 SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))), ": ", ),,2), ",;"),,1), "–"),  "hh:mm")),,9^9)),
 TRANSPOSE(QUERY(TRANSPOSE(TEXT(IFERROR(SPLIT(INDEX(SPLIT(INDEX(
 SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))), ": ", ),,2), ",;"),,2), "–")), "hh:mm")),,9^9))}, 
 "00:00 00:00", ), " ", "-"), 
 COUNTIFS(
 INDEX(SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))), ": ", ),,1), 
 INDEX(SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))), ": ", ),,1), 
      ROW(INDIRECT("A1:A"&COUNTA(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))))),
 "<="&ROW(INDIRECT("A1:A"&COUNTA(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))))))&"♣"&LOWER(
  LEFT(INDEX(SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))), ": ", ),,1), 3))&COLUMN(A:B))), "♣"), 
 "select max(Col3) group by Col1 pivot Col2"), ROW(INDIRECT("A2:A"&COUNTA(A3:A)+ROW(A3)-1)), 0)))

---

UPDATE:

=ARRAYFORMULA(IFERROR(HLOOKUP(B2:O2, IFNA(VLOOKUP({""; ROW(A3:A)}, QUERY(SPLIT(FLATTEN(IF(IFERROR(
 SPLIT(INDEX(SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))), ": ", ),,2), ",;"))<>"", 
 LOWER(LEFT(INDEX(SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, IF(A3:A="",,
 REGEXREPLACE(A3:A, "^|(\n)", "$1"&ROW(A3:A)&"♣"))), CHAR(10))), ": ", ),,1), 5))&COLUMN(A:B)&"♣"&
 SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({TRANSPOSE(QUERY(TRANSPOSE(TEXT(SPLIT(INDEX(SPLIT(INDEX(
 SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))), ": ", ),,2), ",;"),,1), "–"),  "hh:mm")),,9^9)),
 TRANSPOSE(QUERY(TRANSPOSE(TEXT(IFERROR(SPLIT(INDEX(SPLIT(INDEX(
 SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))), ": ", ),,2), ",;"),,2), "–")), "hh:mm")),,9^9))}, 
 "00:00 00:00", ), "Closed 00:00", "Closed"), " ", "-"), 
 LOWER(LEFT(INDEX(SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, IF(A3:A="",,
 REGEXREPLACE(A3:A, "^|(\n)", "$1"&ROW(A3:A)&"♣"))), CHAR(10))), ": ", ),,1), 5))&COLUMN(A:B))), "♣"), 
 "select Col1,max(Col3) group by Col1 pivot Col2"), COLUMN(B:O), 0)), ROW(INDIRECT("A2:A"&COUNTA(A3:A)+ROW(A3)-1)), 0)))

spreadsheet demo

---

FIX:

=ARRAYFORMULA(IFERROR(HLOOKUP(B2:O2, IFNA(VLOOKUP({""; ROW(A3:A)}, QUERY(SPLIT(FLATTEN(IF(IFERROR(
 SPLIT(INDEX(SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))), ": ", ),,2), ",;"))<>"", 
 LOWER(REGEXEXTRACT(INDEX(SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, IF(A3:A="",,
 REGEXREPLACE(A3:A, "^|(\n)", "$1"&ROW(A3:A)&"♣"))), CHAR(10))), ": ", ),,1), "\d+♣..."))&COLUMN(A:B)&"♣"&
 SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({TRANSPOSE(QUERY(TRANSPOSE(TEXT(SPLIT(INDEX(SPLIT(INDEX(
 SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))), ": ", ),,2), ",;"),,1), "–"),  "hh:mm")),,9^9)),
 TRANSPOSE(QUERY(TRANSPOSE(TEXT(IFERROR(SPLIT(INDEX(SPLIT(INDEX(
 SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, A3:A), CHAR(10))), ": ", ),,2), ",;"),,2), "–")), "hh:mm")),,9^9))}, 
 "00:00 00:00", ), "Closed 00:00", "Closed"), " ", "-"), 
 LOWER(REGEXEXTRACT(INDEX(SPLIT(FLATTEN(SPLIT(TEXTJOIN(CHAR(10), 1, IF(A3:A="",,
 REGEXREPLACE(A3:A, "^|(\n)", "$1"&ROW(A3:A)&"♣"))), CHAR(10))), ": ", ),,1), "\d+♣..."))&COLUMN(A:B))), "♣"), 
 "select Col1,max(Col3) group by Col1 pivot Col2"), COLUMN(B:O), 0)), ROW(INDIRECT("A2:A"&MAX(IF(A:A="", ROW(A:A))))), 0)))

Answer 2

Here's another suggestion, which is shorter and accomplishes more.

This is an array formula which will fill a complete row. Then you would just drag it down column B only. I've placed this into a new sheet called "Erik Help," in cell B4:

=ArrayFormula(IF(A4="","",IF(B$3:$3="","",IFERROR(TRIM(SUBSTITUTE(" "&VLOOKUP(LEFT(B$3:$3,3)&"*",{TRIM(QUERY(SPLIT(FLATTEN(SPLIT(A4,";")),": ",0),"Select Col1")),TEXT(TRIM(SPLIT(QUERY(SPLIT(FLATTEN(SPLIT(A4,";")),": ",0),"Select Col2"),"–,",1)),"hh:mm")},IF(VALUE(RIGHT(B$3:$3,1))=1,2,4),FALSE)&"-"&VLOOKUP(LEFT(B$3:$3,3)&"*",{TRIM(QUERY(SPLIT(FLATTEN(SPLIT(A4,";")),": ",0),"Select Col1")),TEXT(TRIM(SPLIT(QUERY(SPLIT(FLATTEN(SPLIT(A4,";")),": ",0),"Select Col2"),"–,",1)),"hh:mm")},IF(VALUE(RIGHT(B$3:$3,1))=1,3,5),FALSE)," -",""))))))

... then I dragged it to B5 and B6.

NOte: FLATTEN is still an unofficial Google function, so use it with this understanding.

Answer 3

I've added another sheet called "Erik Help 2," based on your added comment. The new array formula you'll find there resides in cell B3 (colored cyan). It populates the entire grid of rows and columns, including the sub-headers (e.g., mon1, mon2, etc.) from that one cell:

=ArrayFormula({IF(B$2:O$2<>"",LOWER(LEFT(B$2:O$2,3))&"1",LOWER(LEFT(A$2:N$2,3))&2);IF(TRIM($A4:A)="","",IF(B$2:O$2<>"",
TEXT(TRIM(MID(TRIM($A4:A),FIND(":",TRIM($A4:A),FIND(B$2:O$2,TRIM($A4:A)))+1,9)),"hh:mm")&"-"&TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(MID(TRIM($A4:A),FIND(":",TRIM($A4:A),FIND(B$2:O$2,TRIM($A4:A)))+12,9),",",""),";","")),"hh:mm"),
IF(FIND(";",TRIM($A4:A),FIND(A$2:N$2,TRIM($A4:A)))-FIND(":",TRIM($A4:A),FIND(A$2:N$2,TRIM($A4:A)))>25,
TEXT(TRIM(MID(TRIM($A4:A),FIND(",",TRIM($A4:$A),FIND(A$2:N$2,TRIM($A4:A)))+1,9)),"hh:mm")&"-"&TEXT(TRIM(SUBSTITUTE(MID(TRIM($A4:A),FIND(",",TRIM($A4:$A),FIND(A$2:N$2,TRIM($A4:A)))+12,9),";","")),"hh:mm"),"")))})

You'll want to check it over carefully for accurate results, but it seems right to me.

Keep in mind that this complex formula relies on your keeping the current structure in Column A. That is:

1.) Weekdays are spelled out and followed by a colon and space.

2.) Two periods in the same day are separated by a comma.

3.) Every weekday's entry ends with a semicolon.

If you change this setup, the formula will not work, because it will not be able to find the markers it needs.