Thursday, 13 April 2017

Excel - split e-mail addresses in one cell to separate rows

In Outlook if you copy e-mail addresses from the To or CC lines you end up with something like this:
email01@address.com;email02@address.com;email03@address.com;

Paste the above into Excel and it's the same, all the addresses are in one line and in one cell. What if you'd prefer them in a vertical list like this:
email01@address.com
email02@address.com
email03@address.com

In this article we'll go step-by-step through the process of splitting the addresses from one cell into many rows. 


Instructions


Select the cell containing the e-mail addresses

Click Data | Text to Columns



Click Delimited

Click Next


Click Semicolon

Click Next

Each e-mail address will now appear in separate columns...


Select all the column cells of all the e-mail addresses (as shown above)

Press Ctrl-C to copy them


Click in a cell below (A6 in our example above)

Right click...

Click the Paste Transpose button (as shown above)

The addresses will now be listed on separate rows:


That's it!


Tidy Up
However, there's a space before some of them, if you want to tidy that up, use the Replace option as follows:

Click Home | Find & Select | Replace


In the 'Find what' box put a space, do not enter anything in the 'Replace with' box

Click Replace All

The following is the result:




Conclusion
I hope that was helpful. The above works fine in Excel 2013 but it's very likely to work in Excel 2010 and other versions too.


Post a Comment