Method 1 – Using Combination of Text to Columns and Paste Special Commands
Steps:
- Select the email addresses that you want to paste and press Ctrl+C.
- Paste the emails into the Excel file by pressing Ctrl+V.
- Find that the email addresses are in a horizontal line and separated by semicolons.
- Go to the Data tab.
- From the Data Tools group, select the Text to Columns command.
- A prompt will be on the screen.
- In the prompt, select the Delimited oval.
- Press Next.
- Select Semicolon as Delimiters.
- Click Finish.
- Find that the email addresses are distributed in cells based on semicolons.
- Select the email addresses in the range E5:H5 and press Ctrl+C.
- Select the C5 cell and right-click.
- From the available options, choose Paste Special.
- A prompt will appear on the screen.
- From the prompt, select Transpose.
- Click OK.
- You will see the list of emails in their designated cells.
Method 2 – Using Power Query
Steps:
- Copy the email addresses by clicking Ctrl+C.
- Paste the data into Excel using Ctrl+V.
- Select the data in the B4 and B5 cells.
- Go to the Insert tab.
- From there, select Table.
- A prompt will appear on the screen.
- In the prompt, select your data set as table data to convert the range into a table.
- Click OK.
- The range will turn into a table.
- Select the Table.
- Go to the Data tab.
- Select the From Table/Range command.
- The Power Query window will be opened.
- Go to the Transform tab in the Power Query window.
- Select the Split Column tab.
- From the drop-down list, select By Delimiter.
- A prompt will be on the screen.
- From the prompt, select the Semicolon as a delimiter.
- Select Rows from the Split into command under the Advanced options section.
- Click OK.
- You will get the list of email addresses in a column.
How to Import Contacts from Excel to Outlook
Steps:
- Convert the Excel file into a CSV file.
- Select the File tab.
- Select the Save As command.
- Select CSV (Comma delimited) (*.csv) as the workbook file type.
- Click Save.
- From the prompt, click OK.
- Get the CSV file.
- Open Outlook and select the File tab.
- 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.
- Select Comma Separated Values as the type of file to be imported.
- Browse and insert the file location in the File to import box.
- Choose “Allow duplicate to be created” as the Options.
- Click Next.
- Choose Contacts as the destination folder for the data.
- Click Next.
- Select Map Custom Fields to manually match the fields from your data with the existing fields of Outlook.
- Select the First Name value and drag it onto the field having the same name.
- Do the same for the Last Name.
- Drag the Email value onto the E-mail Address field.
- Drag the Phone Number value onto the Business Phone field.
- Click OK.
- Press Finish.
- Go to the Contacts tab to find the new contacts that are imported from the Excel file.
Download Practice Workbook
You can download the practice workbook here.
Related Articles
- How to Import Data into Excel from Another Excel File
- How to Link PowerPoint Chart to Excel
- How to Extract Data from Image into Excel
<< Go Back to Importing Data in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!