How To Concatenate 1 to 13/any non blank cells groups into adjacent column skipping intermediary blank cells in a GoogleSheets Formula?

Question

My new problem is identical to my previous question asked and kindly answered by @player0 and @TheMaster here:

How To Concatenate multiple non blank cells contents into adjacent column skipping intermediary blank cells in a GoogleSheets Formula?

It is identical in all but the size/length of the non blank cells groups to output. Previously I asked to omit single cells groups as they wouldn't have any subordinate cells content to concatenate with. But that was a mistake as they'd still require laborious manual extraction one-by-one if omitted.

So the new problem is as follow:

In Column C I need to:

• concatenate each vertical non-blank cells groups from Column A (ignoring the blank cells groups in between) AND,
• only concatenate them once (no duplicate smaller groups in-between) AND,
• Any or from 1 to 13 at least groups sizes (that's the only difference needed).

Single Cell Groups remaining issue:

I've looked at new Functions REDUCE and LAMBDA but still need to work on grasping them. @TheMaster thanks for your suggestion, I've tested it but it's not returning any value for the single cell groups (please see the screenshot row 20, E20 doesn't return A20 value. What would be the fix you had in mind (I'm not sure what's the controlling element for the number of row/cells to modify) Thanks again!

Your Formula Tested:

@player0, thanks too for the reply and narrowing down of the formula, though the 2nd shared screenshot appears to be same as 1st one, can't see the change). I reproduced a simplified version of my dataset with the issue in next screenshots and below Text Table:

Your Formula In New Test:

My Regex Formula (To Output Only the Cells With 1s Word followed by 2 whitespaces):

=Arrayformula(if(regexmatch(A1:A,"^(\w+)(\s\s)"),A1:A,""))

My Regex Formula Reversed (To Output the other Cells):

=Arrayformula(if(regexmatch(A1:A,"^(\w+)(\s\s)")=FALSE,A1:A,""))

How To Concatenate multiple non blank cells contents into adjacent column skipping intermediary blank cells in a GoogleSheets Formula?

Text Table Simplified Dataset:

Column A	Column B	Column C	Column D	Column E
A			A
ONE 1, 2, 3, 4, 5, 6, 7, 8,		ONE 1, 2, 3, 4, 5, 6, 7, 8,
&1, 2, 3, 4, 5, 6			&1, 2, 3, 4, 5, 6
TWO 1, 2, 3, 4, 5, 6, 7, 8,		TWO 1, 2, 3, 4, 5, 6, 7, 8,
&1, 2, 3, 4, 5			&1, 2, 3, 4, 5
THREE 1, 2, 3, 4, 5, 6, 7,		THREE 1, 2, 3, 4, 5, 6, 7,
&1, 2, 3, 4, 5, 6, 7,			&1, 2, 3, 4, 5, 6, 7, "	&1, 2, 3, 4, 5, 6, 7,&&1, 2, 3, 4, 5, 6, 7, 8&&&1, 2, 3"
&&1, 2, 3, 4, 5, 6, 7, 8			&&1, 2, 3, 4, 5, 6, 7, 8
&&&1, 2, 3			&&&1, 2, 3
FOUR 1, 2, 3, 4, 5, 6, 7,		FOUR 1, 2, 3, 4, 5, 6, 7,
One, Two, Three, For, Five			One, Two, Three, For, Five
FIVE 1, 2, 3, 4, 5, 6, 7,		FIVE 1, 2, 3, 4, 5, 6, 7,
&1, 2, 3, 4, 5,			&1, 2, 3, 4, 5, "	&1, 2, 3, 4, 5,&&1, 2, 3, 4, 5, 6, 7,&&&1, 2, 3, 4, 5, 6,&&&&1, 2, 3"
&&1, 2, 3, 4, 5, 6, 7,			&&1, 2, 3, 4, 5, 6, 7,
&&&1, 2, 3, 4, 5, 6,			&&&1, 2, 3, 4, 5, 6,
&&&&1, 2, 3			&&&&1, 2, 3

Answer 1

try:

=INDEX(LAMBDA(z, IFNA(VLOOKUP(z, LAMBDA(x, {INDEX(SPLIT(x, " "),,1), 
 SUBSTITUTE(x, " ", )})
 (FLATTEN(SPLIT(QUERY(IF(z="", "​", z),,9^9), "​"))), 2, )))
 (SUBSTITUTE(A2:INDEX(A:A, MAX((A:A<>"")*ROW(A:A))), " ", CHAR(9))))

Answer 2

Thanks to this other solution by @player0

Google sheets split with regex or regexextract?

I finally got it working for the 2nd simplified Dataset as well with those 3 steps:

Adding Heart Symbol to all Cells/lines Ending with no comma (preparation to SPLIT Function later Use Step. vs no heart symbol for the ones ending with comma and outputting them accordingly in Column B with IFS statements:

=ARRAYFORMULA(IFS(A1:A="","",RIGHT(A1:A)<>",",A1:A&"♥",RIGHT(A1:A)=",",A1:A))

Using @player0 above Formula (to apply on Column B instead of A):

=INDEX(LAMBDA(z, IFNA(VLOOKUP(z, LAMBDA(x, {INDEX(SPLIT(x, " "),,1), 
 SUBSTITUTE(x, " ", )})
 (FLATTEN(SPLIT(QUERY(IF(z="", "​", z),,9^9), "​"))), 2, )))
 (SUBSTITUTE(B2:INDEX(B:B, MAX((B:B<>"")*ROW(B:B))), " ", CHAR(9))))

Using REGEXREPLACE as show in related question by Jason:

=TRANSPOSE(SPLIT(REGEXREPLACE(C1, "([a-z]♥[A-Z])", "$1"), "♥"))