Valid email id check

[luxmraj]

Hi,
Is there a quick way I can check if the email id in excel is valid or no. I have a list of 21,000 email ids.
Thanks

[BigBas]

What are the criteria for an email being considered valid?

[luxmraj]

hi,
it should have a domain name and .com. For example:

luxmraj@omron.com.

Thanks

[BigBas]

You can use Data Validation.

Assuming the entry will be in cell A1, use this custom data validation:

=AND(ISNUMBER(FIND("@",A1)),ISNUMBER(SEARCH(".COM",A1)))

Let me know if that works for you.

EDIT: I just reread your request and realized that you want to check existing entries to see if they are legit emails. Just nest the validation formula into an IF statement.

So, in a column next to the email address column, use the formula:

=IF(AND(ISNUMBER(FIND("@",A1)),ISNUMBER(SEARCH(".COM",A1))),"VALID EMAIL","INVALID EMAIL")

[kjxavier]

Great job! Can someone please help with more validations?

I have more validation to be made: That the email address should not contain any non-English characters other than alphabets, numbers, periods(.), hyphens(-) and underscores(_).
where (.COM) is not must, but ("." [period] and "@" ) is for sure.

Please help me out

[janmorris]

@kjxavier

this is an old topic... start your own NEW topic so that people notice it as such