Repetitive Concatenation

All,

I am getting ready to create a spreadsheet to list vendor's names and emails along with other data. I would like to include at the bottom of the email addresses a quick copy and paste distribution list. I have achieved this in the past by using a similar formula:

=CONCATENATE(B3,"; ",B4,"; ",B5)

As you can see it will become very time consuming and not very easy to add names if it gets much longer then 10 names. I know there has to be a way to simplify this process. I have attached an example spreadsheet for your reference. Any and all help is greatly appreciated!

You need a User defined function, I think to do what you need.

If you add this popular code, developed by Harlan Grove, to a new module in the VB editor:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

and then apply like this:

=aconcat(B3:B5,"; ")

Thank you, it works great! The only thing I would like to ask is could you please re-submit with some more comments so I can study the code and understand it better?

I am not an expert in VBA... so not sure if I explain it exactly right...

but basically

Function aconcat(a As Variant, Optional sep As String = "") As String

creates the function and says that the arguments must consist of a variable, in this case "a" is the variable and is defined as a variant or pretty much could be an integer, string, etc...

sep is the variable for the separator you want to use, which has to be string (text) and the default is a null... The whole function results as a string...

The function then determines if A is a range, like A1:C1, if it is it concatenates each of the cell values with whatever separator you chose...

If a is not a range, it checks if it is an array (i think this means, something like A1, A2, A3.. and if so concatenates each with a separator

and lastly if it is a single element it just add the separator to that

Lastly , since concatenation will add a separator to each cell element, it means the last one too will have a separator after.. so the last formula takes the left part of the whole string minus the width of one sepator... so that it effectively removes the last occurrance of a separator at the end of the whole string...

Hope that is all right...


... if someone else can explain it better... please feel free...

Thanks again!