how to validate that email address format is correct or not in xls

Copper Contributor

 

 

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

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)

 

@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.

 

@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)))

@Sergei Baklan 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.