How can i sum up a Textchain in Blocks of 4 and pull down the formula?

[math_g]

Hi,
I would like to display the text from a column (always 4 rows) in a cell and continue this in the cell below.

E.g: Show the text from A1 to A4 in B1, A5 to A8 in B2, A9 to A12 in B3 etc.

The file has almost 1000 rows, so I would like to be able to pull down the formula in column B then.

Is there any way to do this?

[#2 · Accepted solution]

@math_g 

That could be in B1

=CONCAT(INDEX(A:A,(ROW()-1)*4+1):INDEX(A:A,ROW()*4))

and drag it down 

[#3]

@math_g 

And if the TEXTJOIN function is available with the Excel version you are using, you may also try something like this...

=TEXTJOIN(,,OFFSET($A$1,(ROW()-1)*4,0,4))

[#4]

This works pretty well but how do i have to modify the formular if i want to start in row 6?

E.g. A6 to A9 displayed in B6
A10 to A13 in B7
and so on...

[#5]

@math_g 

If you start from another row, we may keep that starting row as parameter in the cell (let say 6 in C1), formula could be

=CONCAT(INDEX(A:A,(ROW()-$C$1)*4+$C$1):INDEX(A:A,(ROW()-$C$1+1)*4+$C$1-1))

or like