Forum Discussion
how to validate that email address format is correct or not in xls
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)))
- juanjorgeJul 26, 2021Copper Contributor
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.