google-sheetslambdagoogle-sheets-formulaconcatenationnonblank

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

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:

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:

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:

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

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

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

My Regex Formula Reversed 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
Solution

  • 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))))

    enter image description here