Forum Discussion

Copper Contributor

Jun 16, 2020

Solved

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

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?

excel

SergeiBaklan
Jun 17, 2020
math_g

That could be in B1

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

and drag it down

4 Replies

Subodh_Tiwari_sktneer
Silver Contributor
Jun 17, 2020
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))
SergeiBaklan
Diamond Contributor
Jun 17, 2020
math_g

That could be in B1

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

and drag it down
- math_g
  Copper Contributor
  Jun 17, 2020
  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...
  - SergeiBaklan
    Diamond Contributor
    Jun 17, 2020
    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