google sheets, splitting and stacking a paragraph

Question

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.

score 3 · Accepted Answer · answered Dec 23 '22 at 11:24

3

try:

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

answered Dec 23 '22 at 11:24

player0

124,011
12
67
124

@jason try: `=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(LAMBDA(x, IF(x="",,A1:A&""&B1:B&""&x)) (SPLIT(C1:C&" ", ". ", ))), ""), "where Col3 is not null", ))` – player0 Dec 23 '22 at 13:57
1

this formula is insanely advanced. Hat offs. If you have time, please explain it. I tried to understand it by rebuilding it from the inside, but it's hard to understand. it looks like you are going line by line and stacking with the split? is that the gist of it? but querying before the stacking to get rid of blanks? – jason Dec 23 '22 at 14:00
@jason your assumption is correct. first we add empty space after each string in B column. then we SPLIT it by combo of dot&space. then to avoid repeating stuff we use LAMBDA and assign variable `x` to the SPLIT fx. (see why lambda: https://stackoverflow.com/a/74393500/5632629). son now we just check with IF if output of split is empty or not, eg. if not then we add to it A column with unique separator (https://stackoverflow.com/a/73967098/5632629) then FLATTEN it and SPLIT it and finally QUERY out empty rows – player0 Dec 23 '22 at 14:36
How is `INDEX` and `ARRAYFORMULA` interchangeable in this case? – jason Dec 23 '22 at 16:48
@jason INDEX is type of ARRAYFORMULA. they are both same at 99% INDEX being shorter to type. the only difference (that 1%) is when it comes at dates where AF is able to keep date's formatting while INDEX will strip the formatting and convert all numeric input to values – player0 Dec 23 '22 at 18:13

score 2 · Answer 2 · answered Dec 23 '22 at 08:26

2

Try below formula-

=QUERY(REDUCE(,B1:B3,LAMBDA(a,x,{a;TRANSPOSE(INDEX(INDEX(A1:A,ROW(x)) & " " & SPLIT(SUBSTITUTE(x,". ",".|"),"|")))})),"offset 1",0)

answered Dec 23 '22 at 08:26

Harun24hr

30,391
4
21
36

score 1 · Answer 3 · answered Dec 23 '22 at 15:52

Here's another formula you can try:

=ARRAYFORMULA(
  QUERY(
    REDUCE({0,0},
           QUERY(A1:A&"❄️"&SPLIT(B1:B,". ",),
                 "where Col1 <> '#VALUE!'"),
           LAMBDA(a,c,
                 {a;SPLIT(c,"❄️",,)})),
    "where Col2 is not null offset 1",))

google sheets, splitting and stacking a paragraph

3 Answers3