Concatenate long list with punctuation (email list)

anyone know how I can concatenate a whole column of email addresses into a single cell with "; " (semicolon space) between each email? I want users -- who are mostly unfamiliar with excel and from a variety of organizations (so I can't do a distribution list) -- to be able to use the most up to date list of members and grab all the emails from one cell (so I can't do a distribution list) and copy/paste it into the outlook send box.
They likely won't understand they can select the column, copy, and paste it because it will look so odd in the send box. I want them to feel safe and comfortable doing it.
The only solution I can find is =concatenate(d5,"; ",d6,"; ",d7,"; ",etc to d45) which would take forever to type in AND wouldn't automatically add in any new members to the list (because d46 isn't in the function).
Is there a better way to do it? (I don't mind doing a series of functions in the background, as long as the cell visible to the group has the complete list of emails).
Thanks!

Look for ConcatAll in this forum ... attributed to TigerAvatar.

Regards, TMS

Will give it a try and let you know. His =ConcatAll(range:range,"; ") didn't work. I'll look at the replies to his post.

This VBA solution will concatenate the list in column D to Cell E1. You can then copy and paste this into Outlook

Option Explicit

Sub email()
    Dim lr As Long
    Dim i As Long
    lr = Range("D" & Rows.Count).End(xlUp).Row
    Dim r As Range
    Set r = Range("E1")
    
    Application.ScreenUpdating = False
    For i = 1 To lr
    r.Value = r.Value & Range("D" & i) & ";"
    Next i
    Application.ScreenUpdating = True

End Sub

Originally Posted by alansidman

This VBA solution will concatenate the list in column D to Cell E1. You can then copy and paste this into Outlook

Option Explicit

Sub email()
    Dim lr As Long
    Dim i As Long
    lr = Range("D" & Rows.Count).End(xlUp).Row
    Dim r As Range
    Set r = Range("E1")
    
    Application.ScreenUpdating = False
    For i = 1 To lr
    r.Value = r.Value & Range("D" & i) & ";"
    Next i
    Application.ScreenUpdating = True

End Sub

Just a heads up...while this is the method I'd personally use, you want to add a line in there to remove the trailing ";" at the end of the string:

Originally Posted by alansidman

Option Explicit

Sub email()
    Dim lr As Long
    Dim i As Long
    lr = Range("D" & Rows.Count).End(xlUp).Row
    Dim r As Range
    Set r = Range("E1")
    
    Application.ScreenUpdating = False
    For i = 1 To lr
    r.Value = r.Value & Range("D" & i) & ";"
    Next i
    r.Value = Left(r.Value,Len(r.Value) - 1)
    Application.ScreenUpdating = True

End Sub

Did you test my VBA solution in #3 post?

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

Here you go...nice and simple. Just drag the formula down and use the last one. Will have them all:

concatenate.JPG

Yields:

concatenate2.jpg

Assuming we are not talking more than 254 cells, you can use this shortcut
Let's say A1:A5 = A, B,C,D,E

1. Enter = CONCATENATE(TRANSPOSE(A1:A5)&"; ")

2. Then in the formula box (white text box where you see the formula), select everything inside the parenthesis and hit F9.
You'll see = CONCATENATE({"A; ", "B; ", "C; ", "D; ", "E; "})
3. Remove the brackets and the final "; "

4. ENTER
How's that work for you?

Note: if you were concatenating a row of cells, no need to use TRANSPOSE but the rest is the same

Such a newbie - how do I mark the thread as 'solved'?

Thread Tools towards the top right of your first post.