Combine text from multiple cells into one cell - =(A1&","&A2","&A3

Discussion in 'Microsoft Excel Worksheet Functions' started by mh, Jul 25, 2009.

  1. mh

    mh Guest

    I would like to combine the words from different cells into one cell -
    sometimes 1 or 2 cells are left empty

    Example
    A1 A2 A3 A4 A5
    Peter Simon Derek Empty Kent

    combine into one cell marking all
    =A1&","&A2&","&A3&","&A4&","&A5

    Should say
    Peter, Simon, Derek, Kent (without extra , or space for empty cell included)

    Could somebody help me with formula for this? would be greatly appreciated
     
    mh, Jul 25, 2009
    #1
    1. Advertisements

  2. mh

    Gord Dibben Guest

    This UDF ignores blank cells.

    Function ConCatRange(CellBlock As Range) As String
    Dim Cell As Range
    Dim sbuf As String
    For Each Cell In CellBlock
    If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & ", "
    Next
    ConCatRange = Left(sbuf, Len(sbuf) - 1)
    End Function

    =concatrange(A1:A5)


    Gord Dibben MS Excel MVP


    On Sat, 25 Jul 2009 10:11:01 -0700, mh <> wrote:

    >I would like to combine the words from different cells into one cell -
    >sometimes 1 or 2 cells are left empty
    >
    >Example
    >A1 A2 A3 A4 A5
    >Peter Simon Derek Empty Kent
    >
    >combine into one cell marking all
    >=A1&","&A2&","&A3&","&A4&","&A5
    >
    >Should say
    >Peter, Simon, Derek, Kent (without extra , or space for empty cell included)
    >
    >Could somebody help me with formula for this? would be greatly appreciated
     
    Gord Dibben, Jul 25, 2009
    #2
    1. Advertisements

  3. Hi,

    Here is a custom function that seems to do what you want:

    Function myConCat(S As Range) As String
    Dim cell As Range
    Dim con As String
    Dim I As Integer
    I = 1
    For Each cell In S
    If I = 1 Then
    con = cell
    ElseIf cell <> "" Then
    con = con & ", " & cell
    End If
    I = I + 1
    Next cell
    myConCat = con
    End Function

    so you would enter
    =myConCat(A1:A10)
    or use any other range.

    --
    If this helps, please click the Yes button.

    Cheers,
    Shane Devenshire


    "mh" wrote:

    > I would like to combine the words from different cells into one cell -
    > sometimes 1 or 2 cells are left empty
    >
    > Example
    > A1 A2 A3 A4 A5
    > Peter Simon Derek Empty Kent
    >
    > combine into one cell marking all
    > =A1&","&A2&","&A3&","&A4&","&A5
    >
    > Should say
    > Peter, Simon, Derek, Kent (without extra , or space for empty cell included)
    >
    > Could somebody help me with formula for this? would be greatly appreciated
    >
     
    Shane Devenshire, Jul 25, 2009
    #3
  4. Also, if I understand your question, you need to make a slight modification
    of the other suggestion:

    =SUBSTITUTE(TRIM(A1&" "&A2&" "&A3&" "&A4&" "&A5)," ",", ")

    note the extra space after the final comma.

    --
    If this helps, please click the Yes button.

    Cheers,
    Shane Devenshire


    "mh" wrote:

    > I would like to combine the words from different cells into one cell -
    > sometimes 1 or 2 cells are left empty
    >
    > Example
    > A1 A2 A3 A4 A5
    > Peter Simon Derek Empty Kent
    >
    > combine into one cell marking all
    > =A1&","&A2&","&A3&","&A4&","&A5
    >
    > Should say
    > Peter, Simon, Derek, Kent (without extra , or space for empty cell included)
    >
    > Could somebody help me with formula for this? would be greatly appreciated
    >
     
    Shane Devenshire, Jul 25, 2009
    #4
  5. Hi,

    You may download and install this addin -
    http://www.download.com/Morefunc/3000-2077_4-10423159.html

    Now use the mconcat(range) function

    --
    Regards,

    Ashish Mathur
    Microsoft Excel MVP
    www.ashishmathur.com

    "mh" <> wrote in message
    news:...
    > I would like to combine the words from different cells into one cell -
    > sometimes 1 or 2 cells are left empty
    >
    > Example
    > A1 A2 A3 A4 A5
    > Peter Simon Derek Empty Kent
    >
    > combine into one cell marking all
    > =A1&","&A2&","&A3&","&A4&","&A5
    >
    > Should say
    > Peter, Simon, Derek, Kent (without extra , or space for empty cell
    > included)
    >
    > Could somebody help me with formula for this? would be greatly appreciated
    >
     
    Ashish Mathur, Jul 27, 2009
    #5
  6. mh

    CavProd

    Joined:
    Jan 11, 2012
    Messages:
    2
    Likes Received:
    0
    I know this post is old, but I'd like to thank you guys for the great formulas. I have one mod I'm hoping someone can help me out with. Right now I'm using the following in VBE:


    Function myConCat(S As Range) As String
    Dim cell As Range
    Dim con As String
    Dim I As Integer
    I = 1
    For Each cell In S
    If I = 1 Then
    con = cell
    ElseIf cell <> "" Then
    con = con & ", " & cell
    End If
    I = I + 1
    Next cell
    myConCat = con
    End Function


    I'd like to be able to combine from 2 or 3 ranges into the same cell so I can exclude a few cells that are acting as headers withing the range. Any way to do this?

    For instance I want cells C5:C11, C20:25 to all show up in the same cell and be separated by commas.
     
    CavProd, Jan 11, 2012
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Michael Tomasura

    Combine multiple cells into one cell

    Michael Tomasura, Aug 23, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    311
  2. Guest

    How to combine multiple cells into 1 cell with comma between each number

    Guest, Mar 18, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    10
    Views:
    11,530
    firefytr
    Mar 19, 2004
  3. Guest

    Combine multiple cells into one cell range.

    Guest, Oct 12, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    217
    Don Guillett
    Oct 12, 2005
  4. Guest

    How do I combine the contents of multiple cells in one cell?

    Guest, Dec 16, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    788
  5. papercut

    Need to combine multiple cells into on cell

    papercut, Jan 15, 2009, in forum: Microsoft Excel Worksheet Functions
    Replies:
    8
    Views:
    263
    papercut
    Jan 15, 2009
Loading...

Share This Page