Concatenating text

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

[dk]

[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

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

[StewartCooke]

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.

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