I am pulling form responses from a sheet, the data is formatted as LastName; FirstName separated by a comma. I wanted to separate the names so each name is in a different cell in column format. On the response sheet, the names are in different rows and different columns each time. The column depends on which form question is answered, and row is your typical form submit. The list of names could be in one of five columns, the other four are blanks. I've compiled this function to generate this example array. I just need to know what else to add to actually stack the columns in a single row. The most obvious way would be to manually write a string literal but ideally, I want the output to produce a single column for me. I've seen examples of people combining columns but they do it with a definitive range. Mine fluctuates by the number of names in columns and the number of columns. I just want to take the columns of output and put it into one column stacked.
My formula for getting data from the form response sheet:
=ArrayFormula(query(trim(split(transpose(query(transpose(A1:D4),,9^99)),",",true,true)), "where Col1 <> '' "))
Example output:
I'm looking to stack these four columns of output into one single column.
