Concatenating text

G

Guest

Guest
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top