Combine rows and add semicolon
 

I have inherited a spreadsheet with a column for e-mail addresses.

I need to combine all of the addresses to send a single e-mail to each
person in the list.

I have tried conctenate and copy down the column thinking it would be
cumulative, but it doesn't seem to work. I think I am messing up the formula
somewhere. Any help would be greatly appreciated. Here is what I had tried:

=CONCATENATE(P2,"; ",P3) then I copy down the column.

Combine rows and add semicolon
 

Try =P2 & ";" & P3
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Lost in Microbiology" wrote
in message ...
I have inherited a spreadsheet with a column for e-mail addresses.

I need to combine all of the addresses to send a single e-mail to each
person in the list.

I have tried conctenate and copy down the column thinking it would be
cumulative, but it doesn't seem to work. I think I am messing up the
formula
somewhere. Any help would be greatly appreciated. Here is what I had
tried:

=CONCATENATE(P2,"; ",P3) then I copy down the column.

Combine rows and add semicolon
 

Try this UDF

Function ConCatRange(CellBlock As Range, Optional Delim As String = "") _
As String
'entered as =concatrange(P2:P23,";" ) desired de-limiter in quotes
Dim Cell As Range
Dim sbuf As String

For Each Cell In CellBlock.Cells
If Cell.text < "" Then
sbuf = sbuf & Cell.text & Delim
End If
Next Cell

ConCatRange = Left(sbuf, Len(sbuf) - Len(Delim))

End Function


Gord Dibben MS Excel MVP


On Tue, 19 May 2009 08:06:01 -0700, Lost in Microbiology
wrote:

I have inherited a spreadsheet with a column for e-mail addresses.

I need to combine all of the addresses to send a single e-mail to each
person in the list.

I have tried conctenate and copy down the column thinking it would be
cumulative, but it doesn't seem to work. I think I am messing up the formula
somewhere. Any help would be greatly appreciated. Here is what I had tried:

=CONCATENATE(P2,"; ",P3) then I copy down the column.