Sometimes you need to create a CSV file to import in Exchange Server. For example, you want to add a new domain to a list of user names. You have exported a list of users. But how to add the same email address to a list of names in Excel? After that, you can import it in Exchange Server.
Export a list of mailboxes to CSV
You can skip this step if you already have a list of user names or email addresses in a CSV file.
Run Exchange Management Shell as administrator. Read more on how to export a list of mailboxes to CSV in Exchange. Create a temp folder in the C: drive. The export will sort the results on PrimarySmtpAddress. It will export the user mailboxes, resource mailboxes, and shared mailboxes to a CSV file.
[PS] C:\>Get-Mailbox -ResultSize Unlimited | Select-Object DisplayName, SamAccountName, PrimarySmtpAddress | Sort-Object PrimarySmtpAddress | Export-CSV c:\temp\list_mailboxes.csv -NoTypeInformation -Encoding UTF8
Before you can add the email address to the list of user names, import the CSV file in Excel.
Import CSV to columns in Microsoft Excel
Double-click the file list_mailboxes.csv with Microsoft Excel. If you don’t have it configured as a default application, right-click the file and open the file with Microsoft Excel. Select the first column by clicking on the column A. Click Data in the ribbon and click Text to Columns.
Choose the file type Delimited. Click Next.
Check the checkbox Comma. Verify the data preview. Click Finish.
Expand the columns to have a clear view of the content in the cells.
Split @domain.com from the names
Select the column with the email addresses. Click Data and click Text to Columns.
Select Delimited and click Next.
Check the checkbox Other and add the @ symbol in the field. Click Finish.
The @domain.com is taken out of the email address. Select the column and remove it.
In the next part, you are going to add the same domain to the list of user names.
Add the same email address to a list of names in Excel
Fill in the formula and press Enter. The formula =C2&”@contoso.com” will take the value from cell C2 and add @contoso.com to it.
After the user name and domain are filled in the cell, you like to fill down the cells. Double-click the fill handle, and it will copy the formula to all the cells in the column.
The result is looking great. All the names are added with the domain contoso.com.
Don’t forget to save the file or copy the content to a new CSV file. I hope that it helped you to add an email address to a list of names in Microsoft Excel.
Read more: Import CSV delimiter in PowerShell »
In this article, you learned how to add an email address to a list of names in Excel. Microsoft Excel is a great tool to edit CSV files. With the right formula, you can speed it up. Use the fill handle in Microsoft Excel to quickly and easily add data to multiple cells.
Did you enjoy this article? If so, you may like Move arbitration mailboxes in Exchange Server. Don’t forget to follow us and share this article.