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.
Download Practice Workbook
You can download the free Excel workbook from here and practice independently.
4 Easy Ways to Concatenate Email Addresses in Excel
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 in Excel (3 Suitable Ways)
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.
Read More: How to Concatenate Multiple Cells in Excel (7 Easy Ways)
Similar Readings
- How to Concatenate Names in Excel (5 Easy Ways)
- Concatenate Cells but Keep Text Formatting in Excel
- How to Concatenate and Keep Number Format in Excel
- Concatenate Date/Day, Month, and Year in Excel (5 Methods)
- How to Concatenate Rows in Excel (11 Methods)
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: Combine Text in Excel (8 Suitable Ways)
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.
Read More: How to Merge Rows in Excel (2 Easy Methods)
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
- Macro to Concatenate Multiple Columns in Excel (with UDF and UserForm)
- How to Concatenate Multiple Cells With Space in Excel (7 Methods)
- Concatenate Multiple Cells Based on Criteria in Excel (4 Methods)
- How to Concatenate Numbers with Leading Zeros in Excel (6 Methods)
- Combine Multiple Columns into One Column in Excel
- Opposite of Concatenate in Excel (4 Options)
- How to Concatenate Two Columns In Excel (5 Methods)