How do I join a range of cells?

[Guest]

I’m trying to combine the values of a range of cells in separate rows, but
can’t get it to work. I can combine 2 rows, but nothing more than that. For
example: I have a spreadsheet with 5 rows made up of one cell each, a1-a5. It
looks like this:

Value1
Value2
Value3
Value4
Value5

I want to put the values of all 5 rows in 1 cell, so it looks like this:

Value1Value2Value3Value4Value5

I’ve tried using =CONCATENATE(A1:A5) but it only results in “Value1â€. Since
my actual spreadsheet has over 42,000 rows it would not be possible from a
sanity standpoint (and Excel won’t allow it anyway) to put
=CONCATENATE(A1,A2,A3,A4,A5) etc.

Any ideas?

 

[Peo Sjoblom]

I am not sure what you want to do, join 42000 cells or join 5 cells, then
the next 5 cells and so on

like

=CONCATENATE(A1,A2,A3,A4,A5)

then

=CONCATENATE(A6,A7,A8,A9,A10)

For obvious reasons number one is not possible (look in help for
specifications)

the latter is possible albeit ugly

=CONCATENATE(OFFSET($A$1,ROWS($A$1:A1)*5-5,),OFFSET($A$2,ROWS($A$1:A1)*5-5,),OFFSET($A$3,ROWS($A$1:A1)*5-5,),OFFSET($A$4,ROWS($A$1:A1)*5-5,),OFFSET($A$5,ROWS($A$1:A1)*5-5,))

copied down.

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com