Forum Discussion

sumitctm's avatar
sumitctm
Copper Contributor
Oct 05, 2017

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 fals...
email
excel
juanjorge's avatar
juanjorge
Copper Contributor
Jul 23, 2021

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.

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 24, 2021

    juanjorge 

    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)))
    • juanjorge's avatar
      juanjorge
      Copper Contributor
      Jul 26, 2021

      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.

Resources