To better explain what result I need, I made a text block below as a pseudo sheet to illustrate.
Col A (source) is the manually entered text data. Col B and C (desired result) will have formulas so that cells from A are populated in them as per the pattern.
Pattern for B is: fixed number of cells from A in its order, followed by delimiter "HELLO" then same number of following cells from A again and so on. Pattern for C is: Text "SET" plus a counter of number of delimited cell sets in B. (Please note that A will have more (or fewer) number of filled cells (added/deleted occasionally), so B & C must adapt accordingly.)
I've been playing around with formula
=arrayformula( if ( mod( row(A:A), 6 ) = 0, "HELLO", A:A) )
for B but it skips every sixth row in A, and also I can't figure out how to do C at all :(
(source) ( desired result )
A B C
1 one one SET 1
2 two two
3 three three
4 four four
5 five five
6 six HELLO
7 seven six SET 2
8 eight seven
9 nine eight
10 ten nine
11 eleven ten
12 twelve HELLO
13 thirteen eleven SET 3
14 fourteen twelve
15 fifteen thirteen
16 sixteen fourteen
17 seventeen fifteen
18 eighteen HELLO
19 nineteen sixteen SET 4
20 twenty seventeen
21 twentyone eighteen
22 twentytwo nineteen
23 twenty
24 HELLO
25 twentyone SET 5
26 twentytwo
27
28
29
... ... ... ...