My new problem is identical to my previous question asked and kindly answered by @player0 and @TheMaster here:
@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 | |||
CodePudding user response：
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))))
