Extract Scattered Email Addresses From Excel Spreadsheet

[steiney]

Hello all,

I have an excel spreadsheet that is an absolute mess, with email addresses scattered about. The columns are A through J, and they all contain email addresses in no particular order or organization. The only constant throughout the spreadsheet is that the cells that contain email address contain ONLY the email address, so there is no other text in those cells.

I am trying to figure out a way to create a formula that will look through all the cells in the spreadsheet and give me a list of every email address found in it.

I have tried for about an hour now googling various search terms and trying formulas and also the "text to columns" tool, but to no avail.

If anyone has any ideas, I would GREATLY appreciate hearing them!

Thanks in advance,

steiney

[Bernie Deitrick]

This will make a list in a free column to the right of your sheet's used range:

Please Login or Register  to view this content.

[og18]

Thank you for addressing this because I too am having this issue. Bernie, can you please explain where you input all this so the email addresses will line up in a free cell? I'm fairly new to creating a formula in excel. I would appreciate your help.

[Bernie Deitrick]

It's not a formula, it's a macro. Copy the code, press Alt-F11, find your workbook in the Explorer window (press Ctrl-R if you do not see that window), use the insert menu, and choose "Module" Then double click that module, and paste the code into the window that appears. Then press F5 and if a window pops up choose "MakeEmailList" from the list and click OK. Then go back to Excel, and press Ctrl-End and page up to the top of that column. The email addresses will start in row 2.