Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

complete Excel novice and not sure if this is possible. In Excel 2010 I need to collate email addresses from an customer address book which is divided into different sheets for A-Z.

Each page of the address book is the same, obviously, so I need to collate a list of the email addresses from column E on each page to a useable list of email addresses. However, not every cell in column E has an email address and Outlook requires a "; " as a seperator for each email address. Can the empty cells be ignored and the seperator inserted for a range like this?

Can the resulting list be on a seperate sheet or will each sheet need it's own list?

share|improve this question
add comment

1 Answer 1

If you are just looking for the list of email address (ie you're not worried about keeping 'fred@example.com' linked to 'Fred Smith') the easiest, but tedious, way to do this is as follows:

Copy column E from the 'A' tab to a new sheet, then sort the entries. This will move all the blanks to the bottom (if it puts them at the top, then sort the list in descending order).

When you've done that select the 'B' sheet and again, copy column E. Go back to your new (sorted) sheet, click in the column where the addresses are, and hit , . This will take you to the last cell with data. Move to the blank cell below it and paste the contents of 'B' in that cell. Sort the column again so all the blanks are at the bottom and proceed with 'C'.

When you're done you should be able to save that list, and then import it.

Let me know if you're not sure about sorting and I'll update.

share|improve this answer
    
thanks for the quick reply. Was looking for a way (if it exists) that would update automatically as the address book was updated rather than manually do the above each time. If that makes sense. –  Chris Dec 7 '12 at 11:56
    
You could save those actions as a macro, and then run the macro every time the file was updated. Then look into how to do loops with the macro so it changes from a, b, c, d, ... x, y, z to a - z. If that makes sense. –  mcalex Dec 7 '12 at 14:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.