Here’s another working smarter with Microsoft Excel tip for changing multiple email addresses when a client, supplier or prospect firm changes their email address format, and/or company name.
To make this simple I am assuming that First name is in column A, Family name is in column B and current email address is in column C in your Excel worksheet.
Change . or – or _ within multiple email addresses in Excel
Change richard_maybury@company.com to richard.maybury@company.com
Create new column or use the next column. I’m calling it D here. I’m changing an _ to a . between first name and family name:
In cell D1 type the following formula:
=A1&”.”&B1&”@company.com”
NOTE: you must use the = sign and the double quotation marks at Shift2
Now, simply hover your cursor over the small box on the bottom right of the D1 cell and drag it down to the end of your list to apply that format to all cells in column D.
It is always a good idea to copy your new email address column data to another column to use them without the formulae. Use right-click ‘Paste special Values (V)’ or, if you are a keyboard jockey, Alt HVV .
Change a full name to the first letter only within multiple email addresses in Excel
Change richard.maybury@company.com to richardm@company.com
Create new column or use the next column. I’m calling it D here. In column D I want to change “Maybury” to “m”. To reduce the family name to the 1st letter only use this formula in cell D1:
=left(b1,1)
Now, create the new email address format in a new column E. In cell E1 use this formula:
=A1&””&D1&”@company.com”
Note: there is no space between the 2 “ marks
Now, simply hover your cursor over the small box on the bottom right of the E1 cell and drag it down to the end of your list to apply that format to all cells in column E.
I hope you find this useful. You might also find these email related Excel posts useful:
How to change a column of Excel email address into Outlook formatted addresses
How to combine 2 or more Excel column data
How to split one column of Excel data into 2 separate columns
Want to improve your time, priority and critical result management skills? Want to leverage your investment in Microsoft Office 365? Want to just deliver your mission critical results easier? Call me on 01428607763 or use the Contact page to get in touch.
Leave A Comment