2

I have imported a text file containing 3 blocks of text. I want to be able to recognize a new block of text, which is always separated by two lines. Then join the text in the rows for that text block and add some text between each line.

Here is the Google Sheet, to make your own copy to have a play or edit. Just go to File -> Make a copy https://docs.google.com/spreadsheets/d/18dAX7P3gCnkUorkYOksyDUfFs_e34uqiwzNjupffo2A/copy

Gracie
  • 896
  • 5
  • 14
  • 34

4 Answers4

1

try like this:

=TEXTJOIN("<break time=""1000ms""/>", 1, A4:A6)
player0
  • 124,011
  • 12
  • 67
  • 124
  • Excellent stuff, is there a way to identify a new text block? Which is always separated by two blank rows. – Gracie Jan 26 '20 at 18:53
0

I have two solutions but they seem tedious, tbh.

[I'd love to see an elegant solution that uses arrayformulas and does the trick with just a single formula in one cell.]

These solutions depend on looking up the two previous rows to see if those were blank.

Link to the sheet with solutions

Solution A

Solution A uses the row number to decide how many rows to concatenate. And then uses TextJoin() to avoid blank rows. The concatenated output is produced at the beginning of each block.

Solution A

Solution B

Solution B continues concatenating text until it finds that the two previous rows were blank. Then it starts afresh. In this case, the concatenated output is produced at the end of each block.

Solution B

PS: For these to work, you'll need two blank rows before the first text block as well.

ADW
  • 4,177
  • 1
  • 14
  • 22
  • This is very good, as you say an ArrayFormula would be idle, but this is looking good. We'll see what else comes in. – Gracie Jan 28 '20 at 13:23
  • @ADW you mentioned wanting to see a single cell solution to this tricky problem. Mine is here on an editable sheet if you're still interested. – MattKing Feb 03 '20 at 12:06
0

I have 1 solution but it has the same limitations as the solution that ADW mentioned and that is that it requires 2 rows before the first text block. However, I managed to place all the formulas into 1 cell. 

=if(AND(A18="",A16="",A17=""),row(),if((A16="")*(A17=""),textjoin("<break time=""1000ms""/>", 1,indirect("A"&row()&":"&concatenate("A",min(arrayformula(if((B19:B109="")*(B20:B110=""),"",row(B18:B))))))),""))

This is the formula for cell B3, and you would just need to drag it down the column.

On a side note, I think that ADW's solutions are great but there is a slight error in ADW's solution A, you would need to add a -1 in the formula, changing it from:

=indirect("A"&row()&":A"&row(indirect("E"&if(B3="","", min(B4:B)))))

to

=indirect("A"&row()&":A"&row(indirect("E"&if(B3="","", min(B4:B)-1))))

I am only commenting here as i have insufficient reputation to comment to his solution.

edmund chan kei yun
  • 111
  • 7
0

Here is my solution sheet. It's just one formula in cell B1. It's editable so you can poke around if you like. Take it for a test drive. The two row boundary between blocks of stuff is important for it's operation.

https://docs.google.com/spreadsheets/d/1sKcBuHwh4v0UECRtUX4mPfE6NOCPkyafhNYs7LSSa0A/edit?usp=sharing

Here's the solution:

=ARRAYFORMULA({"";IF({1;ARRAY_CONSTRAIN(A1:A,ROWS(A3:A),1)&ARRAY_CONSTRAIN(A2:A,ROWS(A3:A),1)=""},TRANSPOSE(SUBSTITUTE(TRIM(QUERY(IF((LOOKUP(ROW(A2:A),{2;FILTER(ROW(A3:A),ARRAY_CONSTRAIN(A1:A,ROWS(A3:A),1)&ARRAY_CONSTRAIN(A2:A,ROWS(A3:A),1)="")})=TRANSPOSE(LOOKUP(ROW(A2:A),{2;FILTER(ROW(A3:A),ARRAY_CONSTRAIN(A1:A,ROWS(A3:A),1)&ARRAY_CONSTRAIN(A2:A,ROWS(A3:A),1)="")})))*(A2:A<>""),A2:A&CHAR(10),),,9^99)),CHAR(10),"<break time="&CHAR(34)&"1000ms"&CHAR(34)&"/>")),)})

Hope it works.

MattKing
  • 7,373
  • 8
  • 13