Search code examples
if-statementgoogle-sheetslambdasplitgoogle-sheets-formula

google sheets, splitting and stacking a paragraph

I have a 3 row by 2 column table

1Q18 hello. testing row one.
2Q18 There are about 7.5b people. That's alot.
3Q18 Last sentence. To be stacking.

I want to split each sentence then have a quarter label with it, out would be

1Q18 hello
1Q18 testing row one
2Q18 There are about 7.5b people
2Q18 That's alot
3Q18 Last sentence
3Q18 To be stacking

I can get one line to work with: =TRANSPOSE({split(rept(A1&" ",counta(split(B1,".")))," ");split(B1,".")}) which would give me:

1Q18 hello
1Q18 testing row one

I need a formula that will let me go down 100 rows, so I can't manually repeat the formula 3 times and use {} with ;

I've also tried using the =map(A1:A,B2:B,LAMBDA(x,y,TRANSPOSE({split(rept(x&" ",counta(split(y,".")))," ");split(y,".")}))) but get a

Error Result should be a single column.

Solution

  • try:

    =INDEX(QUERY(SPLIT(FLATTEN(LAMBDA(x, IF(x="",,A1:A&"​"&x))
 (SPLIT(B1:B&" ", ". ", ))), "​"), "where Col2 is not null", ))

    enter image description here