So what I am trying to do is take a column that is formatted like this:

and write a formula to separate the data into a new column every time there is a space (with the columns ending with the spaces) like this:

Note that the first entry can be a string surrounded by brackets or not, second entry is a number, third entry is a string in that format or blank, and fourth entry is a a string of 1 digit or ">5". Most groups have all 4 entries, but some only have the first two.

I have built a formula that works fine when all groups are the same length, but I need to adapt it so that the next column begins after the space, rather than at the 4th cell every time, otherwise everything starts wrapping around weird. If someone knows how to automatically add empty cells to the "short" groups , that would also fix the problem.

=Transpose(ArrayFormula(IFERROR(vlookup((TRANSPOSE(ROW(INDIRECT("a1:a"&ROUNDUP(COUNTA($B:$B)/6)))-1)*6   ROW(INDIRECT("b1:b"&6)),{ROW($B:$B),$B:$B},2,))))

Thanks for trying to help!

CodePudding user response：

You can try:

=ArrayFormula(TRANSPOSE(SPLIT(TRANSPOSE(SPLIT(TEXTJOIN("@",1,IF(LEN(A1:INDEX(A:A,MAX(ROW(A:A)*(LEN(A:A)>0)),1)),A1:INDEX(A:A,MAX(ROW(A:A)*(LEN(A:A)>0)),1),"#")),"#")),"@")))

CodePudding user response：

use:

=INDEX(SUBSTITUTE(TRANSPOSE(SPLIT(FLATTEN(QUERY(QUERY(QUERY({"", 0, 0; 
 SUBSTITUTE(INDIRECT("A"&ROW(A1)&":A"&MATCH(9, 1/(A:A<>""))), " ", CHAR(13)), 
 COUNTIFS(INDIRECT("A"&ROW(A1)&":A"&MATCH(9, 1/(A:A<>""))), "", 
 SEQUENCE(MATCH(9, 1/(A:A<>""))), "<="&SEQUENCE(MATCH(9, 1/(A:A<>"")))), 
 SEQUENCE(MATCH(9, 1/(A:A<>"")))}, 
 "select max(Col1) group by Col3 pivot Col2"), 
 "offset 1", ),,9^9)), " ")), CHAR(13), " "))

CodePudding user response：

try:

=INDEX(TRANSPOSE(SPLIT(TRIM(FLATTEN(SPLIT(QUERY(
 IF(INDIRECT("A1:A"&MATCH(9, 1/(A:A<>"")))="", "♦", 
 IF(A:A="",,"♣"&A:A)),,9^9), "♦"))), "♣")))

CodePudding user response：

Here's another solution with Regex.

=index(transpose(split(flatten(split(RegexReplace(join("★",if(A:A<>"",A:A,"❆")),"(★❆)(?:★❆) ","$1"),"❆")),"★")))