Google sheets, flatten but with an added line-CodePudding

I have the following data in A1 to C5:

A   B   
C   D   E
F   G   
H       
I   J

I want to flatten this data so that each row is followed by a space, while removing the blank cells like this:

A
B

C
D
E

F
G

H

I
J

But when I use FLATTEN

I get this result:

A
B

C
D
E
F
G

H


I
J

it just treats the blanks in each row as data and slams it together.

I've tried to split and join unsuccessfully using a few variations on:

=BYROW(A1:C5, LAMBDA(row,map(row,LAMBDA(x,if(x="",,x&",")))))

CodePudding user response：

You can try with:

=FLATTEN(SPLIT (JOIN (",",BYROW(A1:C5,LAMBDA(row,TEXTJOIN(",",1,row)&","))),",",1,0))

Joining each row with commas, and an additional comma so it adds the extra cell when splitting