How to Concatenate Email Addresses in Excel (4 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Concatenate Email Addresses in Excel


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.

Using Ampersand to Concatenate Email Addresses in Excel

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.

Using CONCATENATE Function to Combine Email Addresses

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.

Using TEXTJOIN Function to Merge Email Addresses in Excel

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.

Embedding Excel VBA to Merge Email Addresses

  • 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.


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo