Trouble Using CONCATENATE on Every Three Cells

M

marshalgaddis

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)

I am trying merge strings of text from every three cells (i.e. 1,2,3 in one cell, 4,5,6 in the next, 7,8,9 in the third, etc.)

However, once I write the formula and try to apply it to subsequent cells, Excel automatically rewrites the formula to merge three cells beginning with the immediately subsequent cell (i.e. 1,2,3 in one cell, 2,3,4 in the next, 3,4,5 in the third, etc.)

Is there a way to prevent this from happening when I click and drag to apply the formula to an entire column/ row?
 
J

JE McGimpsey

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)

I am trying merge strings of text from every three cells (i.e. 1,2,3 in one
cell, 4,5,6 in the next, 7,8,9 in the third, etc.)

However, once I write the formula and try to apply it to subsequent cells,
Excel automatically rewrites the formula to merge three cells beginning with
the immediately subsequent cell (i.e. 1,2,3 in one cell, 2,3,4 in the next,
3,4,5 in the third, etc.)

Is there a way to prevent this from happening when I click and drag to apply
the formula to an entire column/ row?
Click to expand...

One way:

Assume your values are in A1,A2,A3...Ax

Then in B1:

=INDEX(A:A,(ROW()-1)*3+1) & "," & INDEX(A:A,(ROW()-1)*3+2) & "," &
INDEX(A:A,(ROW()-1)*3+3)

Adjust the constants for your particular situation.
 
Top