+ Reply to Thread
Results 1 to 11 of 11

Combining Value of multiple cells in a single cell

  1. #1
    Forum Contributor
    Join Date
    03-03-2004
    Location
    mumbai
    MS-Off Ver
    2003,2007
    Posts
    151

    Combining Value of multiple cells in a single cell

    Hi,
    I have a spreadsheet wherein it has different values in multiple rows. Now i would like to combine value of different cells into one cell.
    for example.
    In Cell A1 it has "Please"
    in Cell A2 it has "Help"
    in Cell A3 it has "Me"

    then A4 is a balnk row

    now in Cell A5 it has "Welcome"
    and in Cell A6 it has "Guest"
    and after that it has a blank row.

    Now i want a formula which will give me result in Cell B1 as "Please Help Me"(will combine value of A1,A2 and A3)
    and in B5 it will give me "Welcome Guest" (combine A5 and A6)

    Like wise i have huge volume of data in my spreadsheet.
    Last edited by mangesh; 04-20-2011 at 08:14 AM.
    thank you

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Combining Value of multiple cells in a single cell

    b1 = a1&" "&a2&" "&A3
    b5 =a5& " "&a6
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Contributor
    Join Date
    03-03-2004
    Location
    mumbai
    MS-Off Ver
    2003,2007
    Posts
    151

    Re: Combining Value of multiple cells in a single cell

    Thanks for your reply, but there are huge volume, how to determine how many times i have to use concatenate? The example that i have quoted has only 2 examples and had 3 and two rows to combine. Imagine there are multiple rows to be combined, how to do that.

  4. #4
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Combining Value of multiple cells in a single cell

    You will need VBA

    Please Login or Register  to view this content.
    How to use:
    1. Copy above code.
    2. In Excel press Alt + F11 to enter the VBE.
    3. Press Ctrl + R to show the Project Explorer.
    4. Choose Insert -> Module.
    5. Paste code into the right pane.
    6. Press Alt + Q to close the VBE.
    7. Save workbook before any other changes.

    Then in any cell put this formula
    =ConcRange(A3:A6,",")

    Note: it may work slow on huge ranges...
    Last edited by contaminated; 04-20-2011 at 07:26 AM.
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, ?Born in USSR?
    Vusal M Dadashev

    Baku, Azerbaijan

  5. #5
    Forum Contributor
    Join Date
    03-03-2004
    Location
    mumbai
    MS-Off Ver
    2003,2007
    Posts
    151

    Re: Combining Value of multiple cells in a single cell

    Thanks for your help, but how to determine the cell range? In your formula its hard coded,but i need it to be dynamic.
    Last edited by mangesh; 04-20-2011 at 07:43 AM.

  6. #6
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Combining Value of multiple cells in a single cell

    Hi mangesh
    maybe another way
    Please Login or Register  to view this content.
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  7. #7
    Forum Contributor
    Join Date
    03-03-2004
    Location
    mumbai
    MS-Off Ver
    2003,2007
    Posts
    151

    Re: Combining Value of multiple cells in a single cell

    Superb, Thank you friend. Its working the way i wanted it.
    Thanks to other forum contributor as well.

  8. #8
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Combining Value of multiple cells in a single cell

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  9. #9
    Forum Contributor
    Join Date
    03-03-2004
    Location
    mumbai
    MS-Off Ver
    2003,2007
    Posts
    151

    Re: Combining Value of multiple cells in a single cell

    Thank you Pike, that's what i was searching for. Have marked it as Solved.

  10. #10
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: Combining Value of multiple cells in a single cell

    No problem
    Thanks for taking the time to mark the post solved .

  11. #11
    Forum Contributor
    Join Date
    03-03-2004
    Location
    mumbai
    MS-Off Ver
    2003,2007
    Posts
    151

    Re: Combining Value of multiple cells in a single cell

    Thanks to you all guys, always there to help us and simplify the issue. Would request all the users to follow the forum rules diligently, at the end of the day these rules are there to help us.

+ 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