+ Reply to Thread
Results 1 to 19 of 19

CONCATENATE Multiple Cells into One with Comma

  1. #1
    Registered User
    Join Date
    10-30-2012
    Location
    Chicago
    MS-Off Ver
    Excel 365
    Posts
    93

    CONCATENATE Multiple Cells into One with Comma

    I need to combine cells C2, D2, E2, F2 into one cell and then separate each cell content with a comma. Then I have to be able to use the fill handle to drag down for the other rows. Can someone help me with this please? I know how to CONCATENATE with 2 cells, but it's not working for me with a range of cells.
    ANY help would be appreciated.
    Karen
    Last edited by Karen615; 09-11-2013 at 05:31 PM.

  2. #2
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: CONCATENATE Multiple Cells into One with Comma

    Please Login or Register  to view this content.
    Regards
    Sean

    Please add to my reputation if you think i helped
    (click on the star below the post)
    Mark threads as "Solved" if you have your answer
    (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code:
    [code] Your code here [code]
    Please supply a workbook containing example Data:
    It makes its easier to answer your problem & saves time!

  3. #3
    Registered User
    Join Date
    10-30-2012
    Location
    Chicago
    MS-Off Ver
    Excel 365
    Posts
    93

    Re: CONCATENATE Multiple Cells into One with Comma

    Thank you!
    There are some cells that I have nothing to CONCATENATE and this formula returns ,,,
    Then when there are only 2 cells with data, it returns ABC,XYZ,,
    Is there a way to place a space in between each cell data and then remove the unnecessary commas?
    Thanks again for your help.
    Karen

  4. #4
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    566

    Re: CONCATENATE Multiple Cells into One with Comma

    =IF(RIGHT(IF(C2="","",C2&",")&IF(D2="","",D2&",")&IF(E2="","",E2&",")&IF(F2="","",F2),1)=",",LEFT(IF(C2="","",C2&",")&IF(D2="","",D2&",")&IF(E2="","",E2&",")&IF(F2="","",F2),LEN(IF(C2="","",C2&",")&IF(D2="","",D2&",")&IF(E2="","",E2&",")&IF(F2="","",F2))-1),IF(C2="","",C2&",")&IF(D2="","",D2&",")&IF(E2="","",E2&",")&IF(F2="","",F2))

    Long winded way to do this and i'm sure there is something better out there but this will cover for all situations with your text join (avoids multiple commas and removes a starting and finishing comma).
    Say thanks, click *

  5. #5
    Registered User
    Join Date
    10-30-2012
    Location
    Chicago
    MS-Off Ver
    Excel 365
    Posts
    93

    Re: CONCATENATE Multiple Cells into One with Comma

    VERY Fancy! Thank you! It works great!
    Have a great day!

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,391

    Re: CONCATENATE Multiple Cells into One with Comma

    or this...
    =SUBSTITUTE(C2&","&D2&","&E2&","&F2,",,",", ")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: CONCATENATE Multiple Cells into One with Comma

    Try this...

    Data Range
    C
    D
    E
    F
    G
    1
    2
    -----
    Data1
    -----
    Data2
    Data1, Data2

    This formula entered in G2:

    =SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2)," ",", ")

    Copy down as needed.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: CONCATENATE Multiple Cells into One with Comma

    Quote Originally Posted by FDibbins View Post
    or this...
    =SUBSTITUTE(C2&","&D2&","&E2&","&F2,",,",", ")
    Using the sample data in my other reply your formula returns:

    ,Data1, Data2

  9. #9
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: CONCATENATE Multiple Cells into One with Comma

    Quote Originally Posted by Tony Valko View Post
    Try this...

    Data Range
    C
    D
    E
    F
    G
    1
    2
    -----
    Data1
    -----
    Data2
    Data1, Data2

    This formula entered in G2:

    =SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2)," ",", ")

    Copy down as needed.
    Your formula fail if there are more than one word in a cell.

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: CONCATENATE Multiple Cells into One with Comma

    The OP didn't say the data was TEXT so we don't know if there might be more than one word in a cell.

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: CONCATENATE Multiple Cells into One with Comma

    Quote Originally Posted by Tony Valko View Post
    The OP didn't say the data was TEXT so we don't know if there might be more than one word in a cell.
    Well, the OP did say this:

    Then when there are only 2 cells with data, it returns ABC,XYZ,,
    I don't see any multiple word entries, do you?

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,391

    Re: CONCATENATE Multiple Cells into One with Comma

    LOL you arguing with yourself now Biff? (who won?)

  13. #13
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: CONCATENATE Multiple Cells into One with Comma

    Just reviewing the facts as we know them!

    Your formula fail[s]
    Almost any formula will fail if you try hard enough!

  14. #14
    Valued Forum Contributor
    Join Date
    09-15-2011
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    436

    Re: CONCATENATE Multiple Cells into One with Comma

    I have done with VBA (Not invented by me)

    Please try this file.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by ramananhrm; 09-13-2013 at 02:37 AM.
    Please click 'Add reputation', if my answer helped you.

  15. #15
    Registered User
    Join Date
    10-30-2012
    Location
    Chicago
    MS-Off Ver
    Excel 365
    Posts
    93

    Re: CONCATENATE Multiple Cells into One with Comma

    Thank you for your help!

  16. #16
    Registered User
    Join Date
    10-30-2012
    Location
    Chicago
    MS-Off Ver
    Excel 365
    Posts
    93

    Re: CONCATENATE Multiple Cells into One with Comma

    Quote Originally Posted by Tony Valko View Post
    Well, the OP did say this:

    I don't see any multiple word entries, do you?
    There are multiple word entries! I just didn't add the hundreds of rows to my example of data.
    Thanks

  17. #17
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: CONCATENATE Multiple Cells into One with Comma

    Try this...


    Data Range
    C
    D
    E
    F
    G
    1
    2
    Tony Valko
    Deputy Dog
    Teethless Mama
    --------
    Tony Valko, Deputy Dog, Teethless Mama


    This formula entered in G2:

    =TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(C2&","&D2&","&E2&","&F2," ","~"),","," "))," ",", "),"~"," "))

    If you have "many" cells to concatenate (per row) then I would suggest using the UDF located here:

    http://www.excelforum.com/showthread...=1#post3096647
    Last edited by Tony Valko; 09-12-2013 at 10:08 AM.

  18. #18
    Registered User
    Join Date
    04-17-2013
    Location
    North Carolina
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: CONCATENATE Multiple Cells into One with Comma

    Do you have to enter every cell needed or is there a quicker way to have the formula continue for like 36 rows of info?

  19. #19
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: CONCATENATE Multiple Cells into One with Comma

    How about starting your own thread. It's against the formula rules to "hijack" someone else's thread and start asking your own questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Merging multiple cells into a single cell with comma
    By faizaan316 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-03-2013, 04:04 PM
  2. Concatenate matched values and separate by comma
    By scoffman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-15-2012, 10:36 AM
  3. Replies: 7
    Last Post: 11-17-2010, 01:00 PM
  4. Concatenate with comma, then export to text. BORKE!
    By ThinkandDrive in forum Excel General
    Replies: 3
    Last Post: 09-26-2006, 07:02 PM
  5. [SOLVED] how do i concatenate a string and cell formula and use a comma?
    By bob alou in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-24-2006, 11:35 AM

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