software

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?

--
BPB

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

nwexcelsolutions.comquot;BPBquot; gt; wrote in message
...
gt; I'm trying to combine the values of a range of cells in separate rows, but
gt; can't get it to work. I can combine 2 rows, but nothing more than that.
gt; For
gt; example: I have a spreadsheet with 5 rows made up of one cell each, a1-a5.
gt; It
gt; looks like this:
gt;
gt; Value1
gt; Value2
gt; Value3
gt; Value4
gt; Value5
gt;
gt; I want to put the values of all 5 rows in 1 cell, so it looks like this:
gt;
gt; Value1Value2Value3Value4Value5
gt;
gt; I've tried using =CONCATENATE(A1:A5) but it only results in quot;Value1quot;.
gt; Since
gt; my actual spreadsheet has over 42,000 rows it would not be possible from a
gt; sanity standpoint (and Excel won't allow it anyway) to put
gt; =CONCATENATE(A1,A2,A3,A4,A5) etc.
gt;
gt; Any ideas?
gt;
gt; --
gt; BPB