How to Paste a List of Emails into Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Excel users often need to record emails of certain contacts. It not only becomes tiresome to manually enter emails, but also it is prone to mistakes. So, in this article, we will show how to paste a list of emails into Excel from another mailing application.


How to Paste a List of Emails into Excel: 2 Easy Ways

In this article, we will show how to paste a list of emails into Excel from Outlook in 2 different ways. Firstly, we will combine the Text to Columns and the Paste Special commands to paste the list. Then, we will resort to the Power Query to do the task.


1. Using Combination of Text to Columns and Paste Special Commands

The Text to Columns command separates a text with respect to a delimiter. The Paste Special command allows users to paste data according to their preferences. In this method, we will transpose the data using that command.

Steps:

  • Firstly, select the email addresses that you want to paste and press Ctrl+C.

copying email addresses to show how to paste a list of emails into excel

  • After that, paste the emails into the Excel file by pressing Ctrl+V.

pasting copied email addresses to show how to paste a list of emails into excel

  • We will find that the email addresses are in a horizontal line and separated by semicolons.
  • Then, go to the Data tab.
  • From the Data Tools group, select the Text to Columns command.
  • Consequently, a prompt will be on the screen.

selecting text to columns command to show how to paste a list of emails into excel

  • In the prompt, first, select the Delimited oval.
  • Then, press Next.

choosing delimited to show how to paste a list of emails into excel

  • After that, select Semicolon as Delimiters.
  • Finally, click on Finish.

selecting semicolon as delimiter to show how to paste a list of emails into excel

  • As a result, we will find that the email addresses are distributed in cells based on semicolons.
  • Now, select the email addresses in the range E5:H5 and press Ctrl+C.
  • Then, select the C5 cell and right-click.
  • From the available options, choose Paste Special.
  • As a result, a prompt will appear on the screen.

choosing paste special command to show how to paste a list of emails into excel

  • From the prompt, select Transpose.
  • Finally, click OK.

transposing data to show how to paste a list of emails into excel

  • Consequently, we will see the list of emails in their designated cells.

combining the text to columns and paste special commands to show how to paste a list of emails into excel


2. Using Power Query

The Powe Query is a very powerful feature of Excel. It allows users to import data from different sources and different types and manipulate it according to the requirements. In this method, we will use this feature to get our work done.

Steps:

  • To begin with, copy the email addresses by clicking Ctrl+C.

copying the desired email addresses to show how to paste a list of emails into excel

  • Then, paste the data into Excel using Ctrl+V.

pasting email addresses to show how to paste a list of emails into excel

  • After that, select the data in the B4 and B5 cells.
  • Go to the Insert tab.
  • From there, select Table.
  • As a result, a prompt will appear on the screen.

inserting table to show how to paste a list of emails into excel

  • In the prompt, select your data set as data of the table to convert the range into a table.
  • Then, click OK.
  • As a result, the range will turn into a table.

confirming table range to show how to paste a list of emails into excel

  • After that, select the Table.
  • Go to the Data tab.
  • Select the From Table/Range command.
  • As a result, the Power Query window will be opened.

opening power query to show how to paste a list of emails into excel

  • Now, go to the Transform tab in the Power Query window.
  • From there, select the Split Column tab.
  • From the drop-down list, select By Delimiter.
  • As a result, a prompt will be on the screen.

splitting column by delimiter to show how to paste a list of emails into excel

  • From the prompt, first, select the Semicolon as a delimiter.
  • Then, select Rows from the Split into command under the Advanced options section.
  • Finally, click OK.

selecting semicolon as delimiter to show how to paste a list of emails into excel

  • As a result, we will get the list of email addresses in a column.

applying power query to show how to paste a list of emails into excel


How to Import Contacts from Excel to Outlook

We often store a huge amount of contact information in Excel files. It becomes very difficult to contact them just by looking at the Excel files. So, in this method, we will show how to transfer the contacts from Excel to Outlook so that it becomes easier to contact them when needed.

Steps:

  • Firstly, we will convert the Excel file into a CSV file.
  • To do so, select the File tab.

  • Then, select the Save As command.
  • Select CSV (Comma delimited) (*.csv) as the workbook file type.
  • Finally, click Save.

  • After that, from the prompt click OK.

  • As a result, we will get the CSV file.

  • After that, open Outlook and select the File tab.

  • Then, choose the Open & Export option.
  • Select the Import/Export command.

  • From the Import and Export Wizard, choose the “Import from another program or file” option.
  • Choose Next.

  • Then, select Comma Separated Values as the type of file to be imported.
  • After that, browse and insert the file location in the File to import box.
  • Choose “Allow duplicate to be created” as the Options.
  • Click On Next.

  • Afterward, choose Contacts as the destination folder for the data.
  • Click on Next.

  • Select Map Custom Fields to manually match the fields from your data with the existing fields of Outlook.

  • First, select the First Name value and drag it onto the field having the same name.
  • Do the same for the Last Name.

  • Secondly, drag the Email value onto the E-mail Address field.

  • Finally, drag the Phone Number value onto the Business Phone field.
  • Then, click OK.

  • Finally, press Finish.

  • After that, go to the Contacts tab to find the new contacts that are imported from the Excel file.

Read More: How to Copy WhatsApp Group Contacts to Excel


Download Practice Workbook

You can download the practice workbook here.


Conclusion

In this article, we have discussed 2 ways of how to paste a list of emails into Excel. These methods will allow users to paste a large list of emails from any emailing application within minutes and without any error.


Related Articles

<< Go Back to Importing Data in Excel | Learn Excel

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

Adnan Masruf, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. His deep passion for research and innovation seamlessly aligns with his dedication to Excel. In this capacity, Masruf not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, underscoring his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo