+ Reply to Thread
Results 1 to 2 of 2

How do I join a range of cells?

  1. #1
    BPB
    Guest

    How do I join a range of cells?

    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

  2. #2
    Peo Sjoblom
    Guest

    Re: How do I join a range of cells?

    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


    "BPB" <[email protected]> wrote in message
    news:[email protected]...
    > 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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1