Formula that splits data into separate sections based on a divider (blank cell)-CodePudding

I am trying to find a formula which splits data into separate columns based on a blank column.

As this is imported through another sheet and uses a divider:

CodePudding user response：

try:

=INDEX(SUBSTITUTE(TRANSPOSE(IFERROR(SPLIT(TRIM(TRANSPOSE(
 SPLIT(QUERY(SUBSTITUTE(IF(INDIRECT("A2:A"&MAX((ROW(A2:A))*(A2:A<>"")))="", 
 "×", A2:A), " ", "¤"),,9^9), "×"))), " "))), "¤", " "))

update:

=ARRAYFORMULA(IFERROR(1/(1/SUBSTITUTE(TRANSPOSE(IFERROR(SPLIT(TRIM(TRANSPOSE(
 SPLIT(QUERY(SUBSTITUTE(IF(INDIRECT("A1:"&MAX((ROW(A1:A))*(A1:A<>"")))="", 
 "×", A1:A), " ", "¤"),,9^9 0), "×"))), " "))), "¤", " "))))