Combine rows and add semicolon

[Lost in Microbiology]

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.

 

[Bernard Liengme]

Try =P2 & ";" & P3
best wishes

 

[Bernard Liengme]

Try =P2 & ";" & P3
best wishes

 

[Gord Dibben]

Try this UDF

Function ConCatRange(CellBlock As Range, Optional Delim As String = "") _
As String
'entered as =concatrange(P223,";" ) 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

 

[Gord Dibben]

Try this UDF

Function ConCatRange(CellBlock As Range, Optional Delim As String = "") _
As String
'entered as =concatrange(P223,";" ) 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