Create an Email list from Excel

Make an email list from excel

This trick is so handy if you have a table with contacts, or if you’ve exported contacts from Outlook into a table, and you would like to create a block of email addresses separated by semicolons that you can copy-and-paste into the “TO” field of an email. The example I have here only has a few emails (of my favorite female sitcom characters), but you can follow the same steps for a lot more email addresses!

Here’s an example of what you might have in Excel:

Email list from Excel file

If you want to create a block of email addresses (separated by semicolons) that you can copy and paste into the “TO” field of an email, read more

Here’s how you do it:

1. In Excel, highlight the list of email addresses. Start from the last cell in the column and highlight up. Copy and paste the list into Word.

2. In Word, highlight the table and select Table Tools> Layout> Sort (in Word 2003 it’s Table>Sort) and then “OK” (this sorts out all the contacts that don’t have email addresses). Highlight the blank cells and delete them by right clicking and selecting “delete rows.”

Email list from Excel file

3. With the table still highlighted, select Table Tools> Layout> Convert to Text (in Word 2003 it’s Table> Convert >Table to Text).

Email list from Excel file

4. Make sure “paragraph marks” is selected and then hit “OK”

Email list from Excel file

You now have a list of email addresses, separated by paragraph marks (you’re almost there!)

Email list from Excel file

5. Highlight the text and select Home> Replace (or ctrl + f, then select the replace tab). In the “Replace” window, select “More” (see 2). Place the cursor in the “Find what:” field, then select “Special” (3) and “Paragraph Mark” (4).

Email list from Excel file

6. In “Replace with:”, type “; ” (semicolon with a space after it). It should look like this:

Email list from Excel file

Select “Replace All”

7. You now have a list of email addresses, separated by semicolons.

Email list from Excel file

You can copy and paste this in to the “TO” field of an email and send it out!

Email list from Excel file

47 thoughts on “Create an Email list from Excel

  1. Wow, wow, wow, wow!!! I’ve been trying to figure out how to do this forever. I’ve done it through mail merge before, but then I can’t add attachments to it. This is 1000% better.

    Thanks so much!

  2. I am really really almost crying tears of joy here – copy-pasting 300 email-adresses from excel to gmail is really not much fun, and I was facing doing this every other week, if it wasn’t for this blog-post. May the flying Spaghetti-monster bless your ways and guide you! 😉

    serious, really really happy here 😀

  3. This is so incredibly helpful. I think actual users should write program how-to manuals, not the programmers! Thank you so much!

  4. Thank you for saving me literally hours of work! Your tutorial was SO much appreciated, bless you!!

  5. Absolutely great – you don’t know how long and how many times I tried doing this with other methods..unsuccessfully I might add.

  6. I’ve been trying to do this forever! Thank you so much for the visual instructions! A total godsend! WOOOHOO.

  7. Nice method. Another way to do it exclusively in excel is with the concatenate function. In your example you would make D2 = C2. Then D3 = concatenate(D2,”;”,c3).

    Now copy that formula (in D3) all the way down to your last email. The result in Dxx will be a list of all your emails separated by ;. In your example spreadsheet D19 = concatenate(D18,”;”,c19) where D18 will be all the prior emails separated by a ;. Remember you have to copy the formula all the way through.

  8. THANK YOU!!! For years I have been given the tedious task of having to do this whenever it is needed and always thought there had to be a better way! I am so thrilled!! This is a secret to keep so my boss will think I rock!

  9. PERFECT!! Just what I needed to set up a school contact list of 155 email addresses.

    Thank you :-)

  10. Just to echo all the comments made above – thank you for this simple, elegant solution. Why can’t MS Help pages be this good?? Nearly didn’t read this page because of wimpy colours and pallid text (sorry!) – but you have made my day!!

  11. Hi, i managed to come to the end result but unable to have just the semi-colon mark in the final outcome, and would you be able to help me out here, as I need to create a mailing list to send information on a monthly basis to these groups of people who downloaded technical information from our company website. And every week the list of people are different.

    Really appreciate your help. thanks Daryl

  12. Thank you Thank you! You just saved my hide. I made a huge email list (462 people) in excel thinking it would be easy to just stick it in an email….not so. With my boss staring down my back you saved the day!

  13. Thanks so much you’ve just saved be a very boring hours work!!! great that you take time to share this info

  14. I come back to this page over and over! Ha-ha-ha. I love the step by step instructions. Thanks again.

  15. Thank you!! I use Mac and thus do not have outlook, but installed office for Mac… This is tremendously helpful, you are a superstar!!! :-)

  16. How do I do the opposite? I have a list of emails separated by semicolons in a word document i want to convert to excel sheet. pls help. thanks!

  17. Awesome.. but how do you reverse that? I have a list in word and want to copy and paste to excel?

  18. THANK YOU. Saved my day. Wonderful instructions, worked like a charm! Hope you continue to publish these super helpful tips :)

  19. I loved seeing your directions! However…(using MSWord 2010) when I convert to text the paragraph marks don’t show even tho I selected them. Any help would be welcome!

  20. Maaaaaan ….. you are a god send ….. absolutely brilliant help…. saved me a whole lot of effort ….

  21. I have Word 2007 and got lost on step 3 and could not convert table to text. Please advise route for Word 2007. Thank you.

  22. I have been trying to figure that out for a long time. Thank you, thank you, thank you!!! Amazing!!!

  23. Fantastic Article!
    For Word 2007 just click on the Layout Tab and click ‘Convert to Text’ button on the ribbon. Right hand side of screen.

  24. I remember reading this post years ago and using it. Now with a new computer and a new job I couldn’t find the link to this post….searched and search! So glad to have found this again as it is so easy to follow. Bookmarking! Thank you!!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>