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:;;;

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:

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


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:

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