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)))