Forum Discussion
how to validate that email address format is correct or not in xls
Actually, I want to validate the below-given email address format in xls. if the format is correct which means yes then I have to update the value with respect to the email id as true else false.Can anyone pls help me with the correct formula or process.
caria.matt@zew.de
d.gibons@lse.ac.uk
weink@ese.eur.nl
mark@york.ac.uk
joa@gmail.com
4 Replies
sumitctm I created this formula today to validate emails since I was annoyed at Word stopping in the middle of mass mailing to my customers due to invalid emails. I used another formula I found online and I modified it to include also the times when emails had commas or spaces in them, which would make them invalid and stop my Word mail merges.
Formula: =IF(AND(AND(IFERROR(FIND(".",D2),FALSE),IFERROR(FIND(".",D2,FIND("@",D2)),FALSE))=TRUE,iserror(FIND(" ",D2))=TRUE,iserror(find(",",d2))=TRUE), TRUE, FALSE)
This formula assumes the email is in column D, and you just copy it down. If the result is TRUE the email should be valid, if it is FALSE, it is invalid.
Just in case, another notation for your formula could be
=ISNUMBER(FIND(".",D2))* ISNUMBER(FIND(".",D2, FIND("@",D2)))* NOT(ISNUMBER(FIND(" ",D2)))* NOT(ISNUMBER(FIND(",",D2)))SergeiBaklan Thank you. I tried your formula with 3k emails and it gives the same results as mine, difference is that my formula outputs "TRUE" or "FALSE" while yours outputs "1" or "0". And yours is shorter, its nice to have an alternative.
Hi,
It depends on how do you consider if the format is correct. If
- it shall be "@" AND
- at least one dot (".") after the "@"
when
=ISNUMBER(SEARCH("@",A1))*ISNUMBER(SEARCH(".",A1,SEARCH("@",A1)))which returns 1 or 0 (other words TRUE of FALSE)
