Split strings in a google sheet cell into multiple rows?

user_19749677 0
Viewed 36

In this thread I got the correct answer, but when I delete the values in columns B and C there are no results

Question link

Here is the google sheets formula

=INDEX(IFERROR(SPLIT(FLATTEN(FLATTEN(
FILTER(A2:A; A2:A<>"")&"×"&TRANSPOSE(
FILTER(B2:B; B2:B<>"")))&"×"&TRANSPOSE(
FILTER(C2:C; C2:C<>""))), "×"))

INPUT

A B C
car red AA
train yellow BB
blue CC
DD

OUPUT

I want when I delete the values in B and C to have the result like below.

A
car
train

If I just delete the result in column C, I get the result with 6 rows like below.

A B
car red
car yellow
car blue
train red
train yellow
train blue
google-sheets-formula flatten transpose google-sheets arrays
2 Answers

use:

=INDEX(SPLIT(FLATTEN(FLATTEN(
 FILTER(A2:A, A2:A<>"")&     IF(LEN(TRIM(QUERY(B2:B,,9^9))),"×"&TRANSPOSE(
 FILTER(B2:B, B2:B<>"")), ))&IF(LEN(TRIM(QUERY(C2:C,,9^9))),"×"&TRANSPOSE(
 FILTER(C2:C, C2:C<>"")), )), "×"))

enter image description here

enter image description here

enter image description here

user_56326290

Try this

    =INDEX(IFERROR(SPLIT(FLATTEN(FLATTEN(
 FILTER(A1:A, A1:A<>"")&"+_+"&TRANSPOSE(
 FILTER(B1:B, B1:B<>"")))&"+_+"),"+_+")))

enter image description here

user_195296940
Related