how to filtering email with space in email or no @ and dot com

[Thomas_xyz]

Hello everyone,

I'm new to MS Excel and not really know how to go around this but I know that you could solve this either through macro or something. Anyway please bare with me if I seem slow in understanding.

We have thousands of emails in our database through online forms and other sources and it'll take forever to sort through each one at a time. I know that there must be some way that I can use excel to sort all these emails and fix or filter the ones that either have space in the email or filter out the ones that doesn't have an @ symbol or dot com or domain.

thanks in advance for all the help!

[Thomas_xyz]

umm... anyone?

[chris.c]

What result are you looking for? Are you looking to delete certain email addresses? Or are you just hoping to get them at the top of the list?

[Thomas_xyz]

i'm hoping to close the gap in the spacing and those that doesn't have a @ or dotcom i wanted to remove them because the sales team will forward email using the same list

[chris.c]

Do you have a sample spreadsheet? Are you attempting to delete the whole row when there is no @ symbol?

To get rid of the spaces you can use a formula. Let's assume the email addresses are in column A. In cell B1, type:

Please Login or Register  to view this content.

Copy that all the way down.

[Thomas_xyz]

unfortunately i can't disclose the spreadsheet with the emails but we're going to delete teh whole roll

[Thomas_xyz]

eventually, I want to check a excel file and throw out email addresses which are entered like these formats

1) [email protected]
2) [email protected].
3) a [email protected]
4) na
5) n/a

[chris.c]

This should get you started. Change A1 to whatever cell is the first cell with email addresses.

Please Login or Register  to view this content.

[Thomas_xyz]

i found this and it's exactly what i wanted but when i enter it into excel and try to type some invalid email, it just let me go through.

http://www.excelitems.com/2010/11/va...addresses.html

can you take a look at the function they have there? i copied and past that but nothing happen

[chris.c]

It worked fine for me. You need to do the following: Make sure your Excel file is saved with a .xlsm extension (Excel Macro-Enabled Workbook). In the workbook, open the visual basic editor. At the top click "Insert"=>"Module" and paste the code. In the spreadsheet, in whatever cell you choose, type:

Please Login or Register  to view this content.

Replace A1 with the cell that has the email you're trying to check. I attached a workbook with the function in it for you.

[Thomas_xyz]

i see... alright i got it

thank you so much

oh btw are there anyway i can set it to remove the email or the row? because all the data will be enter via website form and whatever that's invalid, i want it to be removed automatically

[chris.c]

Let's say you put the emails in column A and the CheckEmail formula in column B. Then you would use:

Please Login or Register  to view this content.

[chris.c]

Let's say you put the emails in column A and the CheckEmail formula in column B. Then you would use:

Please Login or Register  to view this content.

[Thomas_xyz]

oh wow thanks a lot

[Thomas_xyz]

the code works.
although i realize that a lot of my clients include "http://www." into their emails. are there anyway to modify those emails to remove that?
actually a lot of the emails just need corrections.
in addition to that, some of the email are correct except the domain might be too long and some of them end with .info which was marked invalid.
is there a way where i can just correct these emails?