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

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.

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

Using CONCATENATE Function to Combine Email Addresses

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


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

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.

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.


Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo