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 ... ... ... ...