While mailing a lot of people at a time in Microsoft Outlook, it’s quite boring and time-consuming to insert each email address one by one. If your email addresses are in an Excel worksheet then you can easily overcome this situation by combining them in different ways which are too easy and fast. You are going to learn 4 fast and effective methods from this article to concatenate email addresses in Excel.
How to Concatenate Email Addresses in Excel: 4 Effective WaysÂ
Here’s our sample dataset that we’ll use to explore the methods. It represents some employees’ names and email addresses.
1. Using Ampersand to Concatenate Email Addresses in Excel
In our very first method, we’ll learn how to concatenate multiple email addresses by using an ampersand (&) in an Excel formula. We know, we use it to join strings in Excel. Here, we’ll apply it to join the email addresses by using a semicolon as a delimiter.
Steps:
- In Cell C11, type the following formula-
=C5&";"&C6&";"&C7&";"&C8&";"&C9
- After that, just hit the Enter button to get the merged emails in a string.
Have a look, all the email addresses are now in one string differentiated by a semicolon.
Read More: How to Concatenate Decimal Places in Excel
2. Using CONCATENATE Function to Combine Email Addresses
The CONCATENATE function is the most commonly used function to combine values. Because it is easy to use by inserting the values as arguments.
Steps:
- Insert the following formula in Cell C11–
=CONCATENATE(C5,";",C6,";",C7,";",C8,";",C9)
- Next, to return the output, press the Enter button.
See, this formula is also returning the same output as the previous method.
3. Using TEXTJOIN Function to Merge Email Addresses in Excel
The previous two methods would need to insert the cells individually that’s a little bit time-consuming. Those methods are fine for a little database or split dataset but not feasible for a long dataset. In this case, we can use the TEXTJOIN function to merge a list of emails. Because we can insert the range directly into it.
Steps:
- Activate Cell C11, and write the following formula in it-
=TEXTJOIN(";",TRUE,C5:C9)
- Later, just hit the Enter button to finish.
Read More: How to Concatenate Different Fonts in Excel
4. Embedding Excel VBA to Merge Email Addresses
If you want to concatenate email addresses more smartly and fastly then there is no other way to use Excel VBA. In VBA, we can make a user-defined function (UDF) with a fixed delimiter, so we’ll have to just select the range in the function. Our function name will be Concatenate_EmailAddresses and we’ll use a semicolon (;) as the fixed delimiter in the codes.
Steps:
- Firstly, press Alt + F11 to open the VBA window.
- Then click Insert > Module to insert a new module.
- Later, type the following codes in the module-
Function Concatenate_EmailAddresses(myRange As Range) As String
Dim Con_Cell As Range
For Each Con_Cell In myRange
Concatenate_EmailAddresses = Concatenate_EmailAddresses & ";" & Con_Cell.Value
Next Con_Cell
Concatenate_EmailAddresses = Right(Concatenate_EmailAddresses, Len(Concatenate_EmailAddresses) - 1)
End Function
- After that, no need to run the codes, just go back to your worksheet and you will see that the function is ready.
Here’s our user-defined function (UDF)-
- Finally, insert the following formula in Cell C11 using the function-
=Concatenate_EmailAddresses(C5:C9)
- Press the Enter button to complete.
Download Practice Workbook
You can download the free Excel workbook from here and practice independently.
Conclusion
That’s all for the article. I hope the procedures described above will be good enough to concatenate email addresses in Excel. Feel free to ask any question in the comment section and please give me feedback.
Related Articles
- How to Concatenate Arrays in Excel
- How to Concatenate Cells with If Condition in Excel
- Excel INDEX MATCH to Concatenate Multiple Results
- How to Concatenate If Cell Values Match in Excel
- How to Concatenate with VLOOKUP in Excel
- How to Add Parentheses with CONCATENATE Function in Excel
- Combine CONCATENATE & TRANSPOSE Functions in Excel
<< Go Back to Excel Concatenate Text |Â Concatenate Excel | Learn Excel