Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Concatenating text

  1. #1
    Guest

    Default

    Is there a way to concatenate text from a range of cells (which are either filled with a persons last name, or blank, as determined by an IF statement) and separate them by a comma, if necessary? Its a volatile list of names, so there wont be one if there is only one name, or after the final name appearing in the range, whichever that one may be. Thanks in advance.

    Tim James

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    [quote]
    Is there a way to concatenate text from a range of cells (which are either filled with a persons last name, or blank, as determined by an IF statement) and separate them by a comma, if necessary? Its a volatile list of names, so there wont be one if there is only one name, or after the final name appearing in the range, whichever that one may be. Thanks in advance.

    Tim James
    [quote]

    Tim,

    Try this UDF (User Defined Function). Just paste it into a standard module and then use it in a worksheet cell like this:-

    =SpecialConcat(G345:I345,",")

    You can specify any separator you like, or omit if you don't want one.

    HTH,
    D

    Code:
    Function SpecialConcat(rnge As Range, Optional Seperator As String)
    Dim lr As Long, lc As Long
    
    For lr = 1 To rnge.Rows.Count
        For lc = 1 To rnge.Columns.Count
            SpecialConcat = SpecialConcat & rnge.Cells(lr, lc) & Seperator
        Next lc
    Next lr
    SpecialConcat = Left(SpecialConcat, Len(SpecialConcat) - Len(Seperator))
    End Function


  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Leicestershire, England
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Simply use the "&" symbol to concatenate
    cells.

    i.e. =a1&a2 will concatenate the contents of cells a1 and a2. It doesn't matter if one of the cells is blank.

    You can also add seperators:-

    i.e =a1&" "&a2 which will seperate the names with a space.

  4. #4
    Guest

    Default

    DK - Thanks for the code. That works to a certain degree. If I omit one of the names in the range, the formula still returns a " ," for that cell. Example:
    a1:d1 houses
    {w x y z}
    I need it to say: w,x,y,z
    If I delete the "y" in cell c1, it would report: w,x,z. Currently, it reports w,x,,z

    Similarly, if I delete the x and z in cells b1 and d1, it would report: x not x,,,

    Thanks for your help.

    Tim


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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