Search code examples
arraysgoogle-sheetsfiltertransposeflatten

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


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:

Picture of the image


Solution

  • 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), "×"))), " "))), "¤", " "))
    

    enter image description here


    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), "×"))), " "))), "¤", " "))))
    

    enter image description here