CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
mz1000
mz1000

Reputation: 87

How to dynamically squeeze multiple columns to one in Google Sheets

I have data with blank cells in between and I am looking for a way to squeeze them into one column.

Expected result from the data on the right

Since the number of rows can change and the number of columns can change, too, I am looking for a dynamic way to this. I have already managed to do it in a non-dynamic way using the following formula but I am really interested in a dynamic way.

=IFS(COUNTA($B2:2)=1, TEXTJOIN("",true, $B2:2), COUNTA($B2:2)>1, "AMBIGUOUS")

Upvotes: 1

Views: 244

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IF(REGEXMATCH(TO_TEXT(TRIM(TRANSPOSE(QUERY(TRANSPOSE(SUBSTITUTE(
 INDIRECT("B2:"&ROWS(B:B)), " ", "♦")),,999^99)))), " "), "ambiguous", 
 SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(SUBSTITUTE(
 INDIRECT("B2:"&ROWS(B:B)), " ", "♦")),,999^99))), "♦", " ")))

0

Upvotes: 1

Related Questions